oracle按层级分组,Oracle 复杂的分组

1、高级子查询

exists用来测试存在状态，它以子查询可能放置in的方式放置，不同之处在于，它是对从一个查询返回的行的逻辑测试，而不是对行本身的逻辑测试。

2、外部链接

select a.id from aa a,bb b where a.id(+)=b.id;

select a.id from aa a right outer join bb b on a.id=b.id;

select a.id from aa a right outer join bb b using(id);

用外连接代替not in

用not exists 代替not in

3、自然连接和内部连接

自然连接(natural join)基于表公有的列执行。

select id from a natural join b;

内部连接(inner join)是默认设置的，他们返回两个表公有的行，是除外部连接之外的另一种办法。

4、union(并集)、intersect(交集) 和minus(差)

in语句的优先级总是比union高

1、复杂的分组

select rank(3) within group (order by 列 desc) from 表;

值为3的排在第几行。percent_rank(3)百分比排序。

2、使用临时表

可以为自己的会话创建独立存在的表，或者创建数据在事务处理期间可以永久存在的表。可以使用临时表来支持专门的统计或支持特定的应用处理要求，即使有commit运算结果也不会持久保存。

可以使用create global temporary table 命令创建临时表。在创建临时表时，可以指定它是否在整个会话期间都存在(通过on commit preserve rows子句)，或者在事务处理完成时是否删除它的行(通过on commit delete rows 子句)。

与永久表不同的是，在创建临时表时不会自动分配空间。表的空间是在插入行时动态分配的。

3、使用rollup、grouping和cube

GROUPING函数可以接受一列，返回0或者1。如果列值为空，那么GROUPING()返回1；如果列值非空，那么返回0。GROUPING只能在使用ROLLUP或CUBE的查询中使用。当需要在返回空值的地方显示某个值时，GROUPING()就非常有用。rollup 是用来进行分组的。

rollup

假设有一个表test，有A、B、C、D、E5列。

如果使用group by rollup(A,B,C)，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。roll up的意思是“卷起”，这也可以帮助我们理解group by rollup就是对选择的列从右到左以一次少一列的方式进行grouping直到所有列都去掉后的grouping(也就是全表grouping)，对于n个参数的rollup，有n+1次的grouping。以下2个sql的结果集是一样的：

Select A,B,C,sum(E) from test group by rollup(A,B,C)

与

Select A,B,C,sum(E) from test group by A,B,C

union all

Select A,B,null,sum(E) from test group by A,B

union all

Select A,null,null,sum(E) from test group by A

union all

Select null,null,null,sum(E) from test

cube

cube的意思是立方，对cube的每个参数，都可以理解为取值为参与grouping和不参与grouping两个值的一个维度，然后所有维度取值组合的集合就是grouping的集合，对于n个参数的cube，有2^n次的grouping。如果使用group by cube(A,B,C),，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作，一共是2^3=8次grouping。同rollup一样，也可以用基本的group by加上结果集的union all写出一个与group by cube结果集相同的sql：

Select A,B,C,sum(E) from test group by cube(A,B,C);

与

Select A,B,C,sum(E) from test group by A,B,C

union all

Select A,B,null,sum(E) from test group by A,B

union all

Select A,null,C,sum(E) from test group by A,C

union all

Select A,null,null,sum(E) from test group by A

union all

Select null,B,C,sum(E) from test group by B,C

union all

Select null,B,null,sum(E) from test group by B

union all

Select null,null,C,sum(E) from test group by C

union all

Select null,null,null,sum(E) from test;

grouping sets

grouping sets就是对参数中的每个参数做grouping，也就是有几个参数做几次grouping,例如使用group by grouping sets(A,B,C)，则对(A),(B),(C)进行group by，如果使用group by grouping sets((A,B),C),则对(A,B),(C)进行group by。甚至grouping by grouping set(A,A)都是语法允许的，也就是对(A)进行2次group by,grouping sets的参数允许重复

总结

rollup        (N+1个分组方案)

cube         (2^N个分组方案)

grouping sets (自定义罗列出分组方案)

注意点

1 机制不同

在rollup和cube的说明中分别给出了用基本group by加结果集union all给出了结果集相同的sql，但这只是为了理解的方便而给出的sql，并不说明rollup和cube与基本group by加结果集union all等价。实际上两者的内部机制是安全不一样的，前者除了写法简洁以外，运行时不需多次扫描表，效率远比后者高。

2 集合可运算

3种扩展用法的参数可以是源表中的某一个具体的列，也可以是若干列经过计算而形成的一个新列(比如说A+B，A||B)，也可以是这两种列的一个集合(例如(A+B，C))，对于grouping set更是特殊，可以是空集合()，表示对全表进行group by。

3 group by与rollup, cube组合使用

3)Group by的基本用法以及这3种扩展用法可以组合使用，也就是说可以出现group by A,rollup(A,B)这样的用法，oracle将对出现在group by中的每种用法的grouping列集合做笛卡尔积然后对其中的每一个元素做group by。这话说起来挺绕口，举例说明吧，group by A, rollup(A,B)，基本用法的grouping集合是(A),rollup(A,B)的grouping集合是((A,B),(A),()),两个集合的笛卡尔积集合是((A,A,B),(A,A),(A))，所以会首先对(A,A,B)做group by，然后对(A,A)做group by，最后对(A)做group by。实际上对(A,A,B)做group by和对(A,B)做group by两者是完全等价的(group by A,A,B结果和group by A,B完全一样)，同理对(A,A)做group by和对(A)做group by也是等价的。简化后的结果就是首先对(A,B)做group by，然后对(A)做group by，最后再对(A)做group by。下面给出两个等价的sql以便理解：

Select A,B,sum(E) from test1 group by A, rollup(A,B);

与

Select A,B,sum(E) from test1 group by A,B

Union all

Select A,null,sum(E) from test1 group by A

Union all

Select A,null,sum(E) from test1 group by A;

grouping()、grouping_id()、group_id()

1 grouping()

参数只有一个，而且必须为group by中出现的某一列，表示结果集的一行是否对该列做了grouping。对于对该列做了grouping的行而言，grouping()=0，反之为1；

2 grouping_id()

参数可以是多个，但必须为group by中出现的列。Grouping_id()的返回值其实就是参数中的每列的grouping()值的二进制向量，例如如果grouping(A)=1，grouping(B)=0，则grouping_id(A,B)的返回值就是二进制的10，转成10进制就是2。

3group_id()

无参数。见上面的说明3)，group by对某些列的集合会进行重复的grouping，而实际上绝大多数情况下对结果集中的这些重复行是不需要的，那就必须有办法剔出这些重复grouping的行。当结果集中有n条重复grouping而形成的行时，每行的group_id()分别是0,1,…,n，这样我们在条件中加入一个group_id()<1就可以剔出这些重复grouping的行了。

家族树和connect by

排除个体和分支

使用connect by子句排除2，则22也会被排除，connect by 子句实际上是对树结构进行跟踪。

向根遍历

connect by id =prior pid；就是把id 和pid 互换下位置。

找出叶子节点

oracle database 11g 还有另一个新伪列connect_by_isleaf。如果该行是整个家族的叶子，该列值为1。

基本规则

使用connect by 和start with 来创建类似于树的报表并不难，只要遵循以下基本规则即可：使用connect by 时各子句的顺序应为：

(1)select

(2)from

(3)where

(4)start with

(5)connect by

(6)order byprior强制报表的顺序变为从根到叶(如果prior列是父辈)或从叶到根(如果prior列是后代)

虽然where子句可以从树中排除个体，但不排除他们的子孙(或祖先，如果prior在等号的右边)。

connect by 中的条件(尤其是不等于)可以排除个体和它所有的子孙(或祖先，取决于怎样跟踪树)。

connect by 不能和where子句中的表连接使用。

伪列level和connect_by_isleaf可以帮助在树中筛选出需要的结果。

这是一组很瘦有人能够正确记住的特殊命令。但是，有了对树和继承的基本理解，构造一条合适的select语句以生成树型报表应该很简单。

本公众号是个人学习工作笔记，希望大家发现问题能及时和我本人沟通，希望你与我共同成长。个人微信zgtj12306。

欢迎关注“自学Oracle”