共计 3657 个字符，预计需要花费 10 分钟才能阅读完成。
丸趣 TV 小编给大家分享一下数据库中 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
/
以上是“数据库中 PARTITION BY 分组怎么用”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注丸趣 TV 行业资讯频道！
