Oracle的聚合函数group by结合cube的使用

转自：https://docs.oracle.com/cd/E11882_01/server.112/e25554/aggreg.htm#DWHSG8618

CUBE Syntax

CUBE?appears in the?GROUP?BY?clause in a?SELECT?statement. Its form is:

SELECT …  GROUP BY CUBE (grouping_column_reference_list)

Example 21-4 CUBE

SELECT channel_desc,calendar_month_desc,countries.country_iso_code,TO_CHAR(SUM(amount_sold),‘9,999,999‘) SALES$
FROM sales,customers,times,channels,countries
WHERE sales.time_id=times.time_id AND sales.cust_id=customers.cust_id AND
  sales.channel_id= channels.channel_id
 AND customers.country_id = countries.country_id
 AND channels.channel_desc IN
  (‘Direct Sales‘,‘Internet‘) AND times.calendar_month_desc IN
  (‘2000-09‘,‘2000-10‘) AND countries.country_iso_code IN (‘GB‘,‘US‘)
GROUP BY CUBE(channel_desc,countries.country_iso_code); 

CHANNEL_DESC         CALENDAR CO SALES$
-------------------- -------- -- --------------
                                      1,790,032
                              GB        208,257
                              US      1,581,775
                     2000-09            864,217
                     2000-09  GB        101,792
                     2000-09  US        762,425
                     2000-10            925,815
                     2000-10  GB        106,465
                     2000-10  US        819,351
Internet                                292,387
Internet                      GB         31,109
Internet                      US        261,278
Internet             2000-09            140,793
Internet             2000-09  GB         16,569
Internet             2000-09  US        124,224
Internet             2000-10            151,593
Internet             2000-10  GB         14,539
Internet             2000-10  US        137,054
Direct Sales                          1,497,646
Direct Sales                  GB        177,148
Direct Sales                  US      1,320,497
Direct Sales         2000-09            723,424
Direct Sales         2000-09  GB         85,223
Direct Sales         2000-09  US        638,201
Direct Sales         2000-10            774,222
Direct Sales         2000-10  GB         91,925
Direct Sales         2000-10  US        682,297

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!