SQL 小测验每周一题

luyi6101

ad

luyi6101

abdf

sohay

没底，去验证下。。。

newkid

luyi6101 发表于 2011-11-1 14:54
abdf

回答正确。

2011-6-25 GROUPING SETS

作者：Kim Berg Hansen
难度：中

我创建了如下的表并填入数据：
CREATE TABLE plch_sales
(
   product     VARCHAR2(10)
, country     VARCHAR2(10)
, year        NUMBER
, sales       NUMBER
)
/

BEGIN
   INSERT INTO plch_sales VALUES ('BANANA', 'US', 2009, 200);
   INSERT INTO plch_sales VALUES ('BANANA', 'US', 2010, 300);
   INSERT INTO plch_sales VALUES ('BANANA', 'GB', 2009, 400);
   INSERT INTO plch_sales VALUES ('BANANA', 'GB', 2010, 350);
   INSERT INTO plch_sales VALUES ('BANANA', 'DK', 2010, 250);
   INSERT INTO plch_sales VALUES ('APPLE' , 'US', 2009, 100);
   INSERT INTO plch_sales VALUES ('APPLE' , 'GB', 2009, 150);
   INSERT INTO plch_sales VALUES ('APPLE' , 'GB', 2010, 150);
   INSERT INTO plch_sales VALUES ('APPLE' , 'DK', 2009, 250);
   INSERT INTO plch_sales VALUES ('APPLE' , 'DK', 2010, 250);
   INSERT INTO plch_sales VALUES ('PEAR'  , 'GB', 2010, 150);
   INSERT INTO plch_sales VALUES ('PEAR'  , 'DK', 2009, 300);
   INSERT INTO plch_sales VALUES ('PEAR'  , 'DK', 2010, 350);

   COMMIT;
END;
/

我的老板要我按照产品、国家汇总销售数据，最后必须有总合计。

哪个选项能够产生符合要求的如下输出：
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

(A)
SELECT 'Product' total
     , CASE GROUPING(s.product)
         WHEN 1 THEN 'TOTAL'
         ELSE s.product
       END product
     , 'TOTAL' country
     , SUM(s.sales) sales
FROM plch_sales s
GROUP BY s.product
UNION ALL
SELECT 'Country' total
     , 'TOTAL' product
     , CASE GROUPING(s.country)
         WHEN 1 THEN 'TOTAL'
         ELSE s.country
       END country
     , SUM(s.sales) sales
FROM plch_sales s
GROUP BY s.country
UNION ALL
SELECT 'Grand' total
     , 'TOTAL' product
     , 'TOTAL' country
     , SUM(s.sales) sales
FROM plch_sales s
ORDER BY product, country;

(B)
SELECT * FROM (
   SELECT 'Product' total
        , CASE GROUPING(s.product)
            WHEN 1 THEN 'TOTAL'
            ELSE s.product
          END product
        , 'TOTAL' country
        , SUM(s.sales) sales
   FROM plch_sales s
   GROUP BY s.product
   ORDER BY s.product
)
UNION ALL
SELECT * FROM (
   SELECT 'Country' total
        , 'TOTAL' product
        , CASE GROUPING(s.country)
            WHEN 1 THEN 'TOTAL'
            ELSE s.country
          END country
        , SUM(s.sales) sales
   FROM plch_sales s
   GROUP BY s.country
   ORDER BY s.country
)
UNION ALL
SELECT 'Grand' total
     , 'TOTAL' product
     , 'TOTAL' country
     , SUM(s.sales) sales
FROM plch_sales s;

(C)
SELECT CASE GROUPING_ID(s.product, s.country)
         WHEN 1 THEN 'Product'
         WHEN 2 THEN 'Country'
         WHEN 3 THEN 'Grand'
       END total
     , CASE GROUPING(s.product)
         WHEN 1 THEN 'TOTAL'
         ELSE s.product
       END product
     , CASE GROUPING(s.country)
         WHEN 1 THEN 'TOTAL'
         ELSE s.country
       END country
     , SUM(s.sales) sales
FROM plch_sales s
GROUP BY ROLLUP(s.product, s.country)
ORDER BY s.product, s.country;

(D)
SELECT CASE GROUPING_ID(s.product, s.country)
         WHEN 1 THEN 'Product'
         WHEN 2 THEN 'Country'
         WHEN 3 THEN 'Grand'
       END total
     , CASE GROUPING(s.product)
         WHEN 1 THEN 'TOTAL'
         ELSE s.product
       END product
     , CASE GROUPING(s.country)
         WHEN 1 THEN 'TOTAL'
         ELSE s.country
       END country
     , SUM(s.sales) sales
FROM plch_sales s
GROUP BY GROUPING SETS(
   (s.product),
   (s.country),
   ()
)
ORDER BY s.product, s.country;

(E)
SELECT CASE GROUPING_ID(s.product, s.country)
         WHEN 1 THEN 'Product'
         WHEN 2 THEN 'Country'
         WHEN 3 THEN 'Grand'
       END total
     , CASE GROUPING(s.product)
         WHEN 1 THEN 'TOTAL'
         ELSE s.product
       END product
     , CASE GROUPING(s.country)
         WHEN 1 THEN 'TOTAL'
         ELSE s.country
       END country
     , SUM(s.sales) sales
FROM plch_sales s
GROUP BY CUBE(s.product, s.country)
HAVING GROUPING_ID(s.product, s.country) > 0
ORDER BY s.product, s.country;

(F)
SELECT CASE GROUPING_ID(product, country)
         WHEN 1 THEN 'Product'
         WHEN 2 THEN 'Country'
         WHEN 3 THEN 'Grand'
       END total
     , CASE GROUPING(product)
         WHEN 1 THEN 'TOTAL'
         ELSE product
       END product
     , CASE GROUPING(country)
         WHEN 1 THEN 'TOTAL'
         ELSE country
       END country
     , SUM(sales) sales
FROM plch_sales
GROUP BY GROUPING SETS(
   (product),
   (country),
   ()
)
ORDER BY product, country;

xwren362922604

CD

dingjun123

这个扩展分组题目不错，考察两点：
1.扩展分组中rollup,cube,grouping_sets的使用以及如何补充NULL分组加小计、合计功能（在扩展分组中，NULL分组可以用()代替）以及考察三大函数grouping,grouping_id,group_id
2.考察select经过计算的列与原始列同名，排序不加前缀，那么默认是按select列排序的规则

补充两个，排序就按照steven的排就可以了，当然也可以改进下，反正就几种
SELECT
product,
country,SUM(sales)
FROM plch_sales
GROUP BY GROUPING SETS(rollup(product),rollup(country))
HAVING group_id()=0;

SELECT product,country,SUM(sales)
FROM plch_sales
GROUP BY ROLLUP(product),ROLLUP(country)
HAVING product IS   NULL OR country IS  NULL ;

sd285757

BDF
调试后发现少了一个A选项

xpsun2005

學習了！

likgui

只知道D肯定正确

myth8860

哈，可以拿回去改改做笔试题