共計 3657 個字符，預(yù)計需要花費 10 分鐘才能閱讀完成。
丸趣 TV 小編給大家分享一下數(shù)據(jù)庫中 PARTITION BY 分組怎么用，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！
我在自己的 SCHEMA 下定義了三個表并填入數(shù)據(jù)：客戶表 (plch_customer)，產(chǎn)品表 (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;
/
我們想要一個清單，顯示每種產(chǎn)品賣給每位客戶的總數(shù)量，并有如下需求：
一種產(chǎn)品當(dāng)且僅當(dāng)賣給至少一個客戶時才出現(xiàn)在清單中。
對于清單中出現(xiàn)的產(chǎn)品，售予 plch_customer 表中的每位客戶的數(shù)量都要顯示，如果某客戶沒有購買該產(chǎn)品則顯示 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
下列的哪些語句正確實現(xiàn)了這個需求？
(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
/
以上是“數(shù)據(jù)庫中 PARTITION BY 分組怎么用”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學(xué)習(xí)更多知識，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道！
