oracle partition by group by,PARTITION BY 分组用法

我在自己的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

/