小编给大家分享一下数据库中PARTITION BY分组怎么用，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

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

CREATE TABLE plch_customer (

cust_id INTEGER PRIMARY KEY

, cust_nameVARCHAR2(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_productVALUES (10,'Mouse');

INSERT INTO plch_productVALUES (20,'Keyboard');

INSERT INTO plch_productVALUES (30,'Monitor');

COMMIT;

END;

/

CREATE TABLE plch_sales (

cust_id INTEGER NOT NULL

, prod_id INTEGER NOT NULL

, quantityNUMBERNOT NULL

)

/

BEGIN

INSERT INTO plch_salesVALUES (100, 10, 500);

INSERT INTO plch_salesVALUES (100, 10, 800);

INSERT INTO plch_salesVALUES (100, 20, 600);

INSERT INTO plch_salesVALUES (200, 10, 400);

INSERT INTO plch_salesVALUES (200, 20, 300);

INSERT INTO plch_salesVALUES (200, 20, 700);

INSERT INTO plch_salesVALUES (300, 10, 100);

INSERT INTO plch_salesVALUES (300, 10, 200);

INSERT INTO plch_salesVALUES (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_idcust_id,

s.prod_idprod_id,

SUM(s.quantity)total

FROM plch_sales s

GROUP BY

s.cust_id,

s.prod_id

UNION ALL

SELECT c.cust_idcust_id,

p.prod_idprod_id,

0total

FROM plch_customerc,

( 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_customerc

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 JOINplch_customerc

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

FROMplch_customerc

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_customerc

CROSS JOIN plch_productp

LEFT OUTER JOIN plch_saless

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_productp

LEFT OUTER JOIN plch_saless

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_customerc

LEFT OUTER JOIN plch_saless

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_customerc

CROSS JOIN (SELECT DISTINCT prod_id

FROM plch_sales)p

LEFT OUTER JOIN plch_saless

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

/

以上是“数据库中PARTITION BY分组怎么用”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注亿速云行业资讯频道！