以前做过一段时间的报表,今天面试才发现很多知识掌握的还不是全面的,整理几个知识点.
1.分析函数
1. 自动汇总函数rollup,cube,
2. rank 函数, rank,dense_rank,row_number
3. lag,lead函数
4. sum,avg,的移动增加,移动平均数
5. ratio_to_report报表处理函数
6. first,last取基数的分析函数
2.Combine data from one or more SELECT statements.
SELECT command {UNION | UNION ALL | INTERSECT | MINUS} SELECT commandkey:
UNION - Combine the unique rows returned by 2 SELECT statements
UNION ALL - Combine the rows returned by 2 SELECT statements (including all duplicates)
INTERSECT - Return only those rows that are in *both* SELECT statements
MINUS - Return the rows that are in the first SELECT but not the second
3. START WITH and CONNECT BY in Oracle SQL
The start with .. connect by clause can be used to select data that has a hierarchical relationship (usually some sort of parent->child (boss->employee or thing->parts).
It is also being used when an sql execution plan is explained. recurse-condition can make use of the keyword prior:
