小編給大家分享一下數據庫中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分組怎么用”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
