group by 扩展函数实现报表的需求

最新推荐文章于 2024-04-27 08:54:24 发布

原创最新推荐文章于 2024-04-27 08:54:24 发布 · 466 阅读

0 ·

CC 4.0 BY-SA版权

读书笔记（开发）专栏收录该内容

27 篇文章

订阅专栏

博客展示了数据库表的操作，包括删除表、创建表、插入数据。重点围绕分组统计查询，要求在每组order_book内按日期升序排列，常规分组在前，小计在后，合计最后。通过ROLLUP函数实现分组，结合grouping_id和decode函数进行报表格式化。

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;

/*题目：要求在每组order_book内，按日期升序排列（order_no排序不管）,常规分组在前，小计在后，合计最后。
根据结果可以看出，对于book1来说，前4行就是标准分组，即*/

SELECT t.order_date
      ,t.order_no
      ,t.order_book
      ,SUM(t.order_fee) sum_order_fee
      ,SUM(t.order_num) sum_order_num
FROM t
GROUP BY t.order_book,t.order_date,t.order_no
ORDER BY order_book,order_date

/*第5行小计可以看做是group BY order_book,然后横跨order_time,order_no列的小计，所以也就是*/

SELECT NULL
      ,NULL
      ,t.order_book
      ,SUM(t.order_fee) sum_order_fee
      ,SUM(t.order_num) sum_order_num
FROM t
GROUP BY t.order_book
ORDER BY order_book

/*根据上面两点，可以想到组合列分组，ROLLUP(t.order_book,(t.order_date,t.order_no)),排序是ORDER BY order_book,order_date
即*/

SELECT t.order_date
      ,t.order_no
      ,t.order_book
      ,SUM(t.order_fee) sum_order_fee
      ,SUM(t.order_num) sum_order_num
FROM t
GROUP BY ROLLUP(t.order_book,(t.order_date,t.order_no))
ORDER BY t.order_book,t.order_date

/*最后结合grouping_id来分组*/

SELECT t.order_date
      ,t.order_no
      ,t.order_book
      ,SUM(t.order_fee) sum_order_fee
      ,SUM(t.order_num) sum_order_num
      ,grouping_id(t.order_date,t.order_no,t.order_book)
FROM t
GROUP BY ROLLUP(t.order_book,(t.order_date,t.order_no))
ORDER BY t.order_book,t.order_date

----------------结合上面的结果，使用decode进行报表格式化

SELECT decode(grouping_id(t.order_date,t.order_no,t.order_book),6,order_book||'小计',
                                                                7,'合计',
                                                                to_char(t.order_date,'yyyy-mm-dd')) order_date
      ,t.order_no
      ,decode(grouping_id(t.order_date,t.order_no,t.order_book),0,order_book,
                                                                  NULL) rder_book
      ,SUM(t.order_fee) sum_order_fee
      ,SUM(t.order_num) sum_order_num
FROM t
GROUP BY ROLLUP(t.order_book,(t.order_date,t.order_no))
ORDER BY t.order_book,t.order_date