About me:Oracle ACE,optimistic,passionate and harmonious. Focus on oracle programming,peformance tuning,db design, j2ee,Linux/AIX,web2.0 tech,etc
全部博文（151）
分类： Oracle
2020-06-07 21:03:16
GROUP_ID函数也提供了很好的功能，它无参数，因为扩展GROUP BY分组允许多种复杂分组操作，如部分分组，重复列分组，连接分组等，有时候为了实现复杂的报表功能，会有重复分组统计出现，而GROUP_ID函数就可以区分重复分组结果，第1次出现为0，以后每次出现增1。GROUP_ID在SELECT中出现意义不大，常使用在HAVING中达到过滤重复统计的目的。
现在就用GROUP_ID函数解决1.4.3中的遗留问题，1.4.3使用的是DISTINCT剔除重复行，在扩展分组中，当然最好使用GROUP_ID了，因为GROUP_ID函数就是Oracle设计出来专门用于剔除重复分组结果的，注意DISTINCT是剔除重复行，而不是按重复分组级别剔除的，GROUP_ID则是按重复分组级别来区分的，所以用DISTINCT有时候可能不对，但是用GROUP_ID就没有这个问题了。
SELECT a.dname,b.job, SUM(sal) sum_sal,GROUP_ID() gi
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY GROUPING SETS(ROLLUP(a.dname),ROLLUP(b.job));
显示结果如下：
DNAME JOB SUM_SAL GI
---------------------------- ------------------ ---------- ----------
CLERK 3050 0
SALESMAN 5600 0
PRESIDENT 5000 0
MANAGER 8275 0
ANALYST 3000 0
ACCOUNTING 8750 0
RESEARCH 6775 0
SALES 9400 0
24925 0
24925 1
已选择10行。
看最后两行，第1行的GROUP_ID结果为0，第2次为1，为了获得非重复统计，只要GROUP_ID()=0即可。
SELECT a.dname,b.job, SUM(sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY GROUPING SETS(ROLLUP(a.dname),ROLLUP(b.job))
HAVING GROUP_ID() = 0;
最终剔除重复统计的结果：
DNAME JOB SUM_SAL
---------------------------- ------------------ ----------
CLERK 3050
SALESMAN 5600
PRESIDENT 5000
MANAGER 8275
ANALYST 3000
ACCOUNTING 8750
RESEARCH 6775
SALES 9400
24925
已选择9行。
看看DISTINCT和GROUP_ID的区别：
WITH t
AS
(
SELECT 1 s,'a' name FROM DUAL
UNION ALL
SELECT 1 s,NULL name FROM DUAL
UNION ALL
SELECT 2 s, NULL name FROM DUAL
UNION ALL
SELECT 1 s,'a' name FROM DUAL
)
SELECT s,name,COUNT(1),GROUP_ID()
FROM
t
GROUP BY ROLLUP(s,name);
结果为：
S NA COUNT(1) GROUP_ID()
--------- -- ---------- -------------------------------
1 1 0
1 a 2 0
1 3 0
2 1 0
2 1 0
4 0
已选择6行。
看GROUP_ID都是0，说明无重复分组，如果使用DISTINCT，那么不应该剔除的会剔除，所以还是使用GROUP_ID准确。
GROUPING、GROUPING_ID和GROUP_ID在扩展GROUP BY中的作用很大，用于格式化报表，过滤结果行，排序等很有用，特别是很多扩展分组很复杂，那么使用扩展GROUP BY函数进行一些过滤工作就很有必要了，ROLLUP、CUBE、GROUPING SETS的结果不管是否有默认排序，都是不准确的，需要手动进行有意义的排序，所以必然要使用到扩展GROUP BY函数。到这里，基本的扩展GROUP BY功能已经讲完，扩展GROUP BY功能强大，而且变化万千，所以深刻理解其中的内容，才能灵活运用。
注意：GROUPING和GROUPING_ID函数是没有组合列的，也就是里面不能加括号，比如GROUPING_ID((a,b),c)是错的，而且它们要来自于分组列。
1.7节会结合具体实例讨论扩展GROUP BY的应用，具体见实例。
这个例子是简化的例子，一个简单的订单报表统计，只是为了说明扩展GROUP BY的使用。
DROP TABLE t;
CREATE TABLE t
(
--订购日期
order_date DATE,
--订购号
order_no NUMBER,
--订购书籍
order_book VARCHAR2(10),
--订单总金额
order_fee NUMBER,
--订单明细数目
order_num NUMBER
);
--测试数据
INSERT INTO t
SELECT DATE '2010-5-1'+LEVEL,
TRUNC(DBMS_RANDOM.VALUE *1000),
'book1',100*LEVEL,LEVEL
FROM DUAL
CONNECT BY LEVEL<5;
INSERT INTO t
SELECT DATE '2010-6-1'+LEVEL,
TRUNC(DBMS_RANDOM.VALUE *1000),
'book2',200*LEVEL,LEVEL
FROM DUAL
CONNECT BY LEVEL<5;
COMMIT;
表数据如下：
SCOTT>ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD';
会话已更改。
SCOTT>SELECT * FROM t;
ORDER_DATE ORDER_NO ORDER_BOOK ORDER_FEE ORDER_NUM
---------- ---------- -------------------- ---------- ----------
2010-05-02 315 book1 100 1
2010-05-03 483 book1 200 2
2010-05-04 890 book1 300 3
2010-05-05 604 book1 400 4
2010-06-02 746 book2 200 1
2010-06-03 800 book2 400 2
2010-06-04 758 book2 600 3
2010-06-05 91 book2 800 4
已选择8行。
要求每组order_book内，按日期升序排列（order_no排序不管），小计在后，合计最后。效果如下表所示：
|
ORDER_DATE |
ORDER_NO |
ORDER_BOOK |
SUM_ORDER_FEE |
SUM_ORDER_NUM |
|
2010-05-02 |
315 |
book1 |
100 |
1 |
|
2010-05-03 |
483 |
book1 |
200 |
2 |
|
2010-05-04 |
890 |
book1 |
300 |
3 |
|
2010-05-05 |
604 |
book1 |
400 |
4 |
|
book1小计 |
|
|
1000 |
10 |
|
2010-06-02 |
746 |
book2 |
200 |
1 |
|
2010-06-03 |
800 |
book2 |
400 |
2 |
|
2010-06-04 |
758 |
book2 |
600 |
3 |
|
2010-06-05 |
91 |
book2 |
800 |
4 |
|
book2小计 |
|
|
2000 |
10 |
|
合计 |
|
|
3000 |
20 |
前3列是原始表的列，后两列是聚合结果列。通过分析上面的结果可以看出，使用ROLLUP组合列分组很容易实现，比如对于book1来说，前4行就是标准分组，第5行小计可以看作是GROUP BY order_book,然后计算横跨order_date,order_no列的小计，最后通过DECODE函数+GROUPING_ID函数实现报表格式化，最终结果为：
SELECT DECODE( GROUPING_ID(order_date,order_no,order_book),6,order_book||'小计',
7,'合计',
TO_CHAR(order_date,'yyyy-mm-dd')
) order_date1,
order_no,
DECODE(GROUPING_ID(order_date,order_no,order_book),6,NULL,order_book) order_book1,
SUM(order_fee) order_fee,
SUM(order_num) order_num
FROM t
GROUP BY ROLLUP(order_book,(order_date,order_no))
ORDER BY order_book,order_date;
GROUP BY
ROLLUP(order_book,(order_date,order_no))实现了先标准分组，然后对每个order_book计算横跨order_date和order_no的小计,对于GROUPING_ID(order_date,order_no,order_book)可以区别分组级别，等于6的为小计，等于7的为合计，通过DECODE函数格式化。
（注意：上面的SQL第1列取别名为order_date1,第2列为order_book1,如果不取别名则会覆盖表t的列，那么ORDER BY order_book,order_date排序结果就不符合要求了，或在ORDER
BY中的排序列加前缀t，比如改为ORDER BY
t.order_book,t.order_date也可）
上面的SQL输出结果为：
ORDER_DATE1 ORDER_NO ORDER_BOOK1 ORDER_FEE ORDER_NUM
-------------------------------- ---------- -------------------- ---------- ----------
2010-05-02 315 book1 100 1
2010-05-03 483 book1 200 2
2010-05-04 890 book1 300 3
2010-05-05 604 book1 400 4
book1小计 1000 10
2010-06-02 746 book2 200 1
2010-06-03 800 book2 400 2
2010-06-04 758 book2 600 3
2010-06-05 91 book2 800 4
book2小计 2000 10
合计 3000 20
已选择11行。
结果符合要求，当然，有时候不加ORDER BY也可以默认是上面的排序，但是不准确，想要排序必须加ORDER BY。本章内容已经讲完，里面还有很多细节，还需要读者仔细品味。
