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