小编给大家分享一下数据库中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进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。