ITPub博客

PARTITION BY 分组用法

转载 作者:Oracle单细胞 时间:2018-08-11 11:17:26 0 删除 编辑

我在自己的SCHEMA下定义了三个表并填入数据:客户表(plch_customer),产品表(plch_product),销售表(plch_sales)

CREATE TABLE plch_customer (

  cust_id    INTEGER PRIMARY KEY

, cust_name  VARCHAR2(100)

)

/

BEGIN

   INSERT INTO plch_customer VALUES (100, 'Customer A');

   INSERT INTO plch_customer VALUES (200, 'Customer B');

   INSERT INTO plch_customer VALUES (300, 'Customer C');

   INSERT INTO plch_customer VALUES (400, 'Customer D');

   COMMIT;

END;

/

CREATE TABLE plch_product (

  prod_id   INTEGER PRIMARY KEY

, prod_name VARCHAR2(100)

)

/

BEGIN

   INSERT INTO plch_product  VALUES (10,'Mouse');

   INSERT INTO plch_product  VALUES (20,'Keyboard');

   INSERT INTO plch_product  VALUES (30,'Monitor');

   COMMIT;

END;

/

CREATE TABLE plch_sales (

  cust_id   INTEGER NOT NULL

, prod_id   INTEGER NOT NULL

, quantity  NUMBER  NOT NULL

)

/

BEGIN

   INSERT INTO plch_sales  VALUES (100, 10, 500);

   INSERT INTO plch_sales  VALUES (100, 10, 800);

   INSERT INTO plch_sales  VALUES (100, 20, 600);

   INSERT INTO plch_sales  VALUES (200, 10, 400);

   INSERT INTO plch_sales  VALUES (200, 20, 300);

   INSERT INTO plch_sales  VALUES (200, 20, 700);

   INSERT INTO plch_sales  VALUES (300, 10, 100);

   INSERT INTO plch_sales  VALUES (300, 10, 200);

   INSERT INTO plch_sales  VALUES (300, 10, 900);

   COMMIT;

END;

/

我们想要一个清单,显示每种产品卖给每位客户的总数量,并有如下需求:

一种产品当且仅当卖给至少一个客户时才出现在清单中。

对于清单中出现的产品,售予plch_customer表中的每位客户的数量都要显示,如果某客户没有购买该产品则显示0。

输出如下:

      CUST_ID       PROD_ID         TOTAL

------------- ------------- -------------

          100            10          1300

          100            20           600

          200            10           400

          200            20          1000

          300            10          1200

          300            20             0

          400            10             0

          400            20             0

下列的哪些语句正确实现了这个需求?

(A)

SELECT s.cust_id  cust_id,

       s.prod_id  prod_id,

       SUM(s.quantity)  total

FROM plch_sales s

GROUP BY

       s.cust_id,

       s.prod_id

UNION ALL

SELECT c.cust_id  cust_id,

       p.prod_id  prod_id,

       0  total

FROM plch_customer  c,

   ( SELECT DISTINCT s.prod_id

     FROM plch_sales s )  p

WHERE NOT EXISTS

    ( SELECT '1'

      FROM plch_sales s2

      WHERE s2.cust_id = c.cust_id

      AND   s2.prod_id = p.prod_id )

ORDER BY cust_id, prod_id

/

(B)

SELECT c.cust_id,

       s.prod_id,

       NVL(SUM(s.quantity),0)   total

FROM test.plch_sales s  

     PARTITION BY (s.prod_id)

     RIGHT OUTER JOIN  test.plch_customer  c

  ON ( c.cust_id = s.cust_id )

GROUP BY

       c.cust_id,

       s.prod_id

ORDER BY

       c.cust_id,

       s.prod_id

/

(C)

SELECT c.cust_id,

       s.prod_id,

       NVL(SUM(s.quantity),0)   total

FROM plch_sales s  

     PARTITION BY (s.prod_id)

     LEFT OUTER JOIN  plch_customer  c

  ON ( c.cust_id = s.cust_id )

GROUP BY

       c.cust_id,

       s.prod_id

ORDER BY

       c.cust_id,

       s.prod_id

/

(D)

SELECT c.cust_id,

       s.prod_id,

       NVL(SUM(s.quantity),0)   total

FROM  plch_customer  c

      LEFT OUTER JOIN plch_sales s  

      PARTITION BY (s.prod_id)

  ON ( c.cust_id = s.cust_id )

GROUP BY

       c.cust_id,

       s.prod_id

ORDER BY

       c.cust_id,

       s.prod_id

/

(E)

SELECT c.cust_id,

       p.prod_id,

       NVL(SUM(s.quantity),0)  total

FROM

       plch_customer  c

       CROSS JOIN plch_product  p

       LEFT OUTER JOIN plch_sales  s

       ON (    s.cust_id = c.cust_id

           AND s.prod_id = p.prod_id

          )

GROUP BY

      c.cust_id,

      p.prod_id

ORDER BY

      c.cust_id,

      p.prod_id

/

(F)

SELECT s.cust_id,     

       p.prod_id,

       NVL(SUM(s.quantity),0)  total

FROM

      plch_product  p

      LEFT OUTER JOIN plch_sales  s

      ON ( s.prod_id = p.prod_id )

GROUP BY

      s.cust_id,

      p.prod_id

ORDER BY

      s.cust_id,

      p.prod_id

/

(G)

SELECT c.cust_id,     

       s.prod_id,

       NVL(SUM(s.quantity),0)  total

FROM

      plch_customer  c

      LEFT OUTER JOIN plch_sales  s

      ON ( s.cust_id = c.cust_id )

GROUP BY

      c.cust_id,

      s.prod_id

ORDER BY

      c.cust_id,

      s.prod_id

/

(H)

SELECT c.cust_id,

       p.prod_id,

       NVL(SUM(s.quantity),0)  total

FROM

       plch_customer  c

       CROSS JOIN (SELECT DISTINCT prod_id  

                     FROM plch_sales)  p

       LEFT OUTER JOIN plch_sales  s

       ON (    s.cust_id = c.cust_id

           AND s.prod_id = p.prod_id

          )

GROUP BY

      c.cust_id,

      p.prod_id

ORDER BY

      c.cust_id,

      p.prod_id

/


请登录后发表评论 登录
全部评论

注册时间:2014-07-07

  • 博文量
    17
  • 访问量
    10353