Oracle 高级SQL

简单case和searched case之间的区别：

Case exp when then ; Case when then

1. 简单case只能是when后面的表达式完全匹配case后的表达式，相当于 =，所以也不能匹配null。
2. searched case可以作为比较条件，那么可以使用like，!=,between ..and,<,=，is null,is not null等，比简单case的使用更加广泛，完全可以替代简单case

层次查询

1.

 Connect  by PRIOR ...start with ...

start with确定开始点,Connect  by PRIOR 确定遍历的方向

SELECT LEVEL,ID, NAME
FROM t_test_organ
CONNECT BY PRIOR ID=p_id

START WITH ID='A';

聚合函数

1. 

Group by rollup(...) 一维多层的统计,产生subtotal(小计)的值; rollup( dept,job)=grouping sets((dept,job),(dept),())

Group by cube(...) 多维度的统计,产生cross-tabulation(列联交叉表)的值. cube(dept,job)=grouping sets((dept,job),(dept),(job),())

分析函数

1.row_number() over(order by ...) 仅仅以order by多了一个排序号。排名相同，按rowid去排。

2.row_number() over(partition by ...order by ...) 按分区来取排序号。如果只要前几名的记录，只能用子查询来实现。

SELECT * FROM (select a.deptno, a.job, a.sal, row_number() over(PARTITION BY a.deptno ORDER by a.sal) NUM from t_test_organ a) WHERE NUM<2;

3. rank() over(order by ...)排名相同会留一个空位。如1,1,3

4. dense_rank() over(order by ...)排名相同不会留一个空位。如1,1,2

5. percent_rank() over(order by ...)，返回某个值相对于一组值的百分比排名。

6.CUME_DIST() over(order by ...)，返回特定值相对于一组值的位置

7.ntile(..)over(order by ...)，每一条分组纪录返回一个数字

8.   MAX or min or AVG(a.sal) keep(dense_rank first order by sal desc) over(partition by a.deptno) first, 取first value
      MAX or min or AVG(a.sal) keep(dense_rank last order by sal desc) over(partition by a.deptno) last, 取last value

MODEL函数

model典型使用场景。合计行追加 行列变换 使用当前行的前后行 RegExp_Replace函数的循环执行