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

博客展示了数据库表的操作,包括删除表、创建表、插入数据。重点围绕分组统计查询,要求在每组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


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值