最近一直在pub的开发版上转悠，跟高手们学到了很多东西，下面是其中的一道sql方面的题，在此转一下。
表中数据如下：
SQL> select * from plch_sales s;
PRODUCT COUNTRY YEAR SALES
---------- ---------- ---------- ----------
BANANA US 2009 200
BANANA US 2010 300
BANANA GB 2009 400
BANANA GB 2010 350
BANANA DK 2010 250
APPLE US 2009 100
APPLE GB 2009 150
APPLE GB 2010 150
APPLE DK 2009 250
APPLE DK 2010 250
PEAR GB 2010 150
PEAR DK 2009 300
PEAR DK 2010 350
13 rows selected.
要实现如下结果：
TOTAL PRODUCT COUNTRY SALES
------- ---------- ---------- ----------
Product APPLE TOTAL 900
Product BANANA TOTAL 1500
Product PEAR TOTAL 800
Country TOTAL DK 1400
Country TOTAL GB 1200
Country TOTAL US 600
Grand TOTAL TOTAL 3200
-----用group by cube实现
SQL> select grouping_id(s.product,s.country),grouping(s.product),grouping(s.country),s.product,s.country,sum(s.sales) sales
2 from plch_sales s
3 group by cube(s.product, s.country);
GROUPING_ID(S.PRODUCT,S.COUNTRY) GROUPING(S.PRODUCT) GROUPING(S.COUNTRY) PRODUCT COUNTRY SALES
-------------------------------- ------------------- ------------------- ---------- ---------- ----------
3 1 1 3200
2 1 0 DK 1400
2 1 0 GB 1200
2 1 0 US 600
1 0 1 PEAR 800
0 0 0 PEAR DK 650
0 0 0 PEAR GB 150
1 0 1 APPLE 900
0 0 0 APPLE DK 500
0 0 0 APPLE GB 300
0 0 0 APPLE US 100
1 0 1 BANANA 1500
0 0 0 BANANA DK 250
0 0 0 BANANA GB 750
0 0 0 BANANA US 500
15 rows selected.
SQL> select grouping_id(s.product,s.country),grouping(s.product),grouping(s.country),s.product,s.country,sum(s.sales) sales
2 from plch_sales s
3 group by cube(s.product, s.country)
4 HAVING GROUPING_ID(s.product, s.country) > 0
5 ORDER BY s.product, s.country;
GROUPING_ID(S.PRODUCT,S.COUNTRY) GROUPING(S.PRODUCT) GROUPING(S.COUNTRY) PRODUCT COUNTRY SALES
-------------------------------- ------------------- ------------------- ---------- ---------- ----------
1 0 1 APPLE 900
1 0 1 BANANA 1500
1 0 1 PEAR 800
2 1 0 DK 1400
2 1 0 GB 1200
2 1 0 US 600
3 1 1 3200
7 rows selected.
SQL> SELECT CASE GROUPING_ID(s.product, s.country)
2 WHEN 1 THEN 'Product'
3 WHEN 2 THEN 'Country'
4 WHEN 3 THEN 'Grand'
5 END total
6 , CASE GROUPING(s.product)
7 WHEN 1 THEN 'TOTAL'
8 ELSE s.product
9 END product
10 , CASE GROUPING(s.country)
11 WHEN 1 THEN 'TOTAL'
12 ELSE s.country
13 END country
14 , SUM(s.sales) sales
15 FROM plch_sales s
16 GROUP BY CUBE(s.product, s.country)
17 HAVING GROUPING_ID(s.product, s.country) > 0
18 ORDER BY s.product, s.country;
TOTAL PRODUCT COUNTRY SALES
------- ---------- ---------- ----------
Product APPLE TOTAL 900
Product BANANA TOTAL 1500
Product PEAR TOTAL 800
Country TOTAL DK 1400
Country TOTAL GB 1200
Country TOTAL US 600
Grand TOTAL TOTAL 3200
7 rows selected.
-----用grouping sets实现
SQL> select grouping_id(s.product,s.country),grouping(product),grouping(country),product,country,sum(sales)
2 from plch_sales s
3 group by grouping sets((s.product),(s.country),());
GROUPING_ID(S.PRODUCT,S.COUNTR GROUPING(PRODUCT) GROUPING(COUNTRY) PRODUCT COUNTRY SUM(SALES)
------------------------------ ----------------- ----------------- ---------- ---------- ----------
1 0 1 PEAR 800
1 0 1 BANANA 1500
1 0 1 APPLE 900
2 1 0 DK 1400
2 1 0 GB 1200
2 1 0 US 600
3 1 1 3200
7 rows selected.
SQL> select decode(grouping_id(s.product,s.country),1,'Product',2,'Country',3,'Grand') total,
2 decode(grouping(product),0,product,1,'Total') product,
3 decode(grouping(country),0,country,1,'Total') country,
4 sum(sales)
5 from plch_sales s
6 group by grouping sets((s.product),(s.country),())
7 ORDER BY s.product, s.country;
TOTAL PRODUCT COUNTRY SUM(SALES)
------- ---------- ---------- ----------
Product APPLE Total 900
Product BANANA Total 1500
Product PEAR Total 800
Country Total DK 1400
Country Total GB 1200
Country Total US 600
Grand Total Total 3200
7 rows selected.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/10972173/viewspace-711584/ ，如需转载，请注明出处，否则将追究法律责任。
