Question

如何实现比较复杂的分组，小计与合计

Answer 1

按group by rollup(stock_id,type_cd,DISCOUNT);分组，其他的业务计算你就自己写吧

具体如下：

SQL> select * from t_dist;

TYPE_CD BUYER_ID ORDER_DT SO_ID STOCK_ID UNIT_PRICE DISCOUNT QTY
---------- ------------ ----------- ------------ ------------ ---------- ---------- ----------
1 CN1001 2008-4-1 S9001 29110311 50 10 8
1 CN1001 2008-4-2 S9002 29110312 60 20 2
1 CN1001 2008-4-3 S9003 29110313 70 15 3
2 CN1001 2008-4-4 S9004 29110312 60 15 5
2 CN1001 2008-4-5 S9005 29110311 70 10 6
3 CN1001 2008-4-6 S9006 29110313 55 20 4
3 CN1001 2008-4-6 S9007 29110311 40 10 3
3 CN1001 2008-4-7 S9008 29110312 50 50 5
3 CN1001 2008-4-7 S9009 29110313 80 10 2
1 CN1001 2008-4-8 S9010 29110311 65 10 1

10 rows selected

SQL>
SQL> select decode(rn, 1, '小计', STOCK_ID) STOCK_ID,
2 TYPE_CD,
3 DISCOUNT,
4 AVG_PRICE,
5 SUM_TOT,
6 round(nvl(RATIO_TO_REPORT(decode(rn, 0, SUM_TOT, null)) over(partition by STOCK_ID), 1) * 100, 2) || '%' PCT
7 from
8 (select STOCK_ID,
9 TYPE_CD,
10 DISCOUNT,
11 round(avg(UNIT_PRICE), 2) AVG_PRICE,
12 sum(QTY * UNIT_PRICE) SUM_TOT,
13 grouping(TYPE_CD) rn
14 from t_dist
15 group by grouping sets((STOCK_ID), (STOCK_ID, TYPE_CD, DISCOUNT)));

STOCK_ID TYPE_CD DISCOUNT AVG_PRICE SUM_TOT PCT
------------ ---------- ---------- ---------- ---------- -----------------------------------------
29110311 1 10 57.5 465 46.27%
29110311 2 10 70 420 41.79%
29110311 3 10 40 120 11.94%
小计 56.25 1005 100%
29110312 1 20 60 120 17.91%
29110312 2 15 60 300 44.78%
29110312 3 50 50 250 37.31%
小计 56.67 670 100%
29110313 1 15 70 210 35.59%
29110313 3 10 80 160 27.12%
29110313 3 20 55 220 37.29%
小计 68.33 590 100%

12 rows selected

SQL>