wonderful-sql：(4)集合运算
【摘要】 小学三年级学过的集合知识， 您还记得 Venn 图吗？
《SQL基础教程（第2版）》：https://www.ituring.com.cn/book/1880
《DataWhale SQL 基础教程》：https://fastly.jsdelivr.net/gh/datawhalechina/wonderful-sql@main/materials/SQL%E5%9F%BA%E7%A1%80%E6%95%99%E7%A8%8B-V2.0.pdf
wonderful-sql：https://github.com/datawhalechina/wonderful-sql
这是笔者参与开源学习《wonderful-sql》的学习记录，希望通过分享自己的学习过程能够帮助到零基础的同学快速入门 SQL。
本次学习借助开源学习社区 DataWhale，不仅有优秀的助教辅导，还有优秀的小伙伴一起学习，计划大概如下：
| 任务信息 | 截止时间 | 进度 |
|---|---|---|
| 07 月 20 日正式开始, 预计学习 16 天， 每天 2 小时 左右 | ||
| Task01：环境搭建、初识数据库（2 天） | 截止时间 07 月 22 日 03:00 | ✅ |
| Task02：基础查询与排序（2 天） | 截止时间 07 月 24 日 03:00 | ✅ |
| Task03：复杂一点的查询（3 天） | 截止时间 07 月 27 日 03:00 | ✅ |
| Task04：集合运算（3 天） | 截止时间 07 月 30 日 03:00 | ✅ |
| Task05：SQL 高级处理（3 天） | 截止时间 08 月 02 日 03:00 | 🗙 |
| Task06：秋招秘籍 ABC（3 天） | 截止时间 08 月 05 日 03:00 | 🗙 |
前言
SQL开源学习进程过半，社群从报名的 148 人到现在还有 50 个小伙伴在坚持打卡，相信大家都能坚持学完。截止到此时此刻，已经有 30 个小伙伴打卡了，卷锝卷中卷，方为王中王！鉴于时间关系，本次 Task 的学习，笔者只能囫囵吞枣，依旧先是过了一遍教程理解知识点，接着跟着敲代码熟悉 SQL。本次笔记依旧从习题入手，并从习题中回顾集合运算相关的知识点。
习题1
题目：
假设连锁店想要增加成本利润率超过 50%或者售价低于 800 的货物的存货量, 请使用 UNION 对分别满足上述两个条件的商品的查询结果求并集。
结果应该类似于:
+------------+--------------+--------------+------------+----------------+
| product_id | product_name | product_type | sale_price | purchase_price |
+------------+--------------+--------------+------------+----------------+
| 0001 | T恤衫 | 衣服 | 1000 | 500 |
| 0002 | 打孔器 | 办公用品 | 500 | 320 |
| 0006 | 叉子 | 厨房用具 | 500 | NULL |
| 0008 | 圆珠笔 | 办公用品 | 100 | NULL |
+------------+--------------+--------------+------------+----------------+
知识点
首先我们先回顾一下我们小学三年级学过的集合知识， 还记得 Venn 图吗？，问：三年级（1）的同学爱读故事书的有30人，爱读画册的人有25人，两种书都爱读的有10人，问这个班共有多少人？
这个问题就体现了集合运算，集合在数学领域中表示指具有某种特定性质的具体的或抽象的对象汇总成的集体。我们不纠结于集合本身，而关注的是 SQL 中的集合运算：
- UNION：并集操作，返回两个查询的结果集的并集，自动去除重复行。相比谓词OR，效率更高，且可跨表查询
- UNION ALL：并集操作，返回两个查询的结果集的并集，不会去除重复行。
- INTERSECT：交集操作，返回两个查询的结果集的交集。
- EXCEPT：差集操作，返回在第一个查询结果集中存在，而在第二个查询结果集中不存在的行。
解题
SELECT product_id,product_name,product_type,sale_price,purchase_price
FROM product
WHERE sale_price > 1.5 * purchase_price
UNION
SELECT product_id,product_name,product_type,sale_price,purchase_price
FROM product
WHERE sale_price < 800;
习题2
题目：
分别使用 UNION 或者 OR 谓词,找出成本利润率不足 30%或成本利润率未知的商品。
知识点
谓词就是返回值为真值的函数。包括TRUE / FALSE / UNKNOWN，谓词 OR 相关的知识点请查看上一 Task。
从 SQL 运行效率来看，使用UNION 用时 (0.00 sec)， 使用 OR 谓词 用时 (0.01 sec)，这还只是查询到 4 条数据的情况。
解题
-- 使用 UNION
SELECT * FROM product
WHERE sale_price < 1.3 * purchase_price
UNION
SELECT * FROM product
WHERE sale_price / purchase_price IS NULL;
-- 使用 OR 谓词
SELECT * FROM product
WHERE sale_price < 1.3 * purchase_price
OR sale_price / purchase_price IS NULL;
习题3
题目：
商店决定对product表中成本利润低于50% **或者** 售价低于1000的商品提价, 请使用UNION ALL 语句将分别满足上述两个条件的结果取并集. 查询结果类似下表:
+------------+--------------+--------------+------------+----------------+-------------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date |
+------------+--------------+--------------+------------+----------------+-------------+
| 0003 | 运动T恤 | 衣服 | 4000 | 2800 | NULL |
| 0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20 |
| 0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-01-15 |
| 0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28 |
| 0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 |
| 0006 | 叉子 | 厨房用具 | 500 | NULL | 2009-09-20 |
| 0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28 |
| 0008 | 圆珠笔 | 办公用品 | 100 | NULL | 2009-11-11 |
+------------+--------------+--------------+------------+----------------+-------------+
知识点
UNION ALL 语句 是 SQL 的一种集合运算，它用于组合两个或更多 SELECT 语句的结果集。与 UNION 不同的是，UNION ALL 不会去除结果集中的重复行。
注意：在使用 UNION ALL 时，每个 SELECT 语句必须有相同数量的列。列也必须具有相似的数据类型。同时，列的顺序也必须一致。
解题
-- 使用 UNION ALL
SELECT * FROM product
WHERE sale_price < 1.5 * purchase_price
UNION ALL
SELECT * FROM product
WHERE sale_price < 1000;
习题4
题目：
使用 SYSDATE()函数可以返回当前日期时间, 是一个日期时间类型的数据, 试测试该数据类型和数值,字符串等类型的兼容性。
知识点
隐式数据类型转换，在 SQL 中，当两种不同的数据类型需要在一个操作中一起使用时，通常会发生隐式数据类型转换，也就是数据库自动将一种数据类型转换为另一种数据类型(PS:有点类似弱类型编程语言JavaScript)。
例如：
mysql> SELECT '100' + 50;
+------------+
| '100' + 50 |
+------------+
| 150 |
+------------+
1 row in set (0.01 sec)
**注意：隐式转换的行为可能因数据库的类型（如 MySQL、PostgreSQL、SQL Server 等）和设置的不同而不同。在编写涉及不同数据类型的 SQL 语句时，最好明确进行数据类型转换，以避免可能的错误和混淆。 **
数据倾斜（Data Skew）是指在数据集中，数据的分布不均匀。在大数据处理过程中，可能会出现。
解题
SELECT SYSDATE(), SYSDATE(), SYSDATE(), SYSDATE()
UNION
SELECT 'chars', 123, NULL, TRUE;
当前 SQL 版本下，时间日期类型和字符串,数值以及缺失值均能兼容。
习题5
题目：
找出只存在于Product表但不存在于Product2表的商品。
知识点
INTERSECT：交集运算
EXCEPT：差集运算
注意： 只有在 MySQL 高版本（8.0.31 ）才支持
解题
-- 使用差集运算 EXCEPT
SELECT * FROM product
EXCEPT
SELECT * FROM product2;
-- 使用 NOT IN 谓词
SELECT * FROM product
WHERE product_id
NOT IN (SELECT product_id FROM product2);
习题6
题目：
使用NOT谓词进行集合的减法运算, 求出 Product 表中, 售价高于2000、成本利润率不低于 30% 的商品, 结果应该如下表所示。
知识点
在 SQL 中，NOT 谓词可以用于实现集合的减法运算，这通常与 EXISTS 或 IN 谓词结合使用。这种方式可以实现 EXCEPT 的功能，即返回在第一个查询结果集中存在，而在第二个查询结果集中不存在的行（差集）。
解题
-- 使用 NOT IN 谓词
SELECT * FROM product
WHERE sale_price > 2000
AND product_id
NOT IN (SELECT product_id FROM product WHERE sale_price < 1.3 * purchase_price);
-- 使用 EXCEPT 运算符
SELECT * FROM product
WHERE sale_price > 2000
EXCEPT
SELECT * FROM product
WHERE sale_price < 1.3 * purchase_price;
习题7
题目：
使用AND谓词查找product表中利润率高于50%,并且售价低于1500的商品,查询结果如下所示。
+------------+--------------+--------------+------------+----------------+-------------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date |
+------------+--------------+--------------+------------+----------------+-------------+
| 0001 | T恤衫 | 衣服 | 1000 | 500 | 2009-09-20 |
| 0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 |
+------------+--------------+--------------+------------+----------------+-------------+
知识点
INTERSECT 和 AND 谓词在 SQL 中都可以用来获取两个条件都满足的结果，但它们的使用场景和方式有所不同。
INTERSECT 是集合运算符，用于获取两个 SELECT 语句结果集的交集。
而 AND 是逻辑运算符，用于在同一个 SELECT 语句中组合多个条件。只有当所有条件都为真时，行才会被选中。
解题
-- 使用 AND 谓词
SELECT * FROM product
WHERE sale_price > 1.5 * purchase_price
AND sale_price < 1500;
-- 使用 INTERSECT 运算符
SELECT * FROM product
WHERE sale_price > 1.5 * purchase_price
INTERSECT
SELECT * FROM product
WHERE sale_price < 1500;
习题8
题目：
使用Product表和Product2表的对称差来查询哪些商品只在其中一张表, 结果类似于:
+------------+--------------+--------------+------------+----------------+-------------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date |
+------------+--------------+--------------+------------+----------------+-------------+
| 0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20 |
| 0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-01-15 |
| 0006 | 叉子 | 厨房用具 | 500 | NULL | 2009-09-20 |
| 0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28 |
| 0008 | 圆珠笔 | 办公用品 | 100 | NULL | 2009-11-11 |
| 0009 | 手套 | 衣服 | 800 | 500 | NULL |
| 0010 | 水壶 | 厨房用品 | 2000 | 1700 | 2009-09-20 |
+------------+--------------+--------------+------------+----------------+-------------+
提示: 使用 NOT IN 实现两个表的差集.
知识点
**对称差（Symmetric Difference）**是集合论中的一个概念，对于两个集合 A 和 B，它们的对称差定义为只属于 A 或只属于 B 的元素组成的集合。
在 SQL 中，我们可以通过 UNION ALL 和 EXCEPT 来实现对称差的计算。
注意：在使用这种方法时，每个 SELECT 语句必须有相同数量的列。列也必须具有相似的数据类型。同时，列的顺序也必须一致。
对称差也可以使用 NOT IN来实现。
解题
-- 使用 NOT IN 实现两个表的差集
SELECT * FROM product
WHERE product_id NOT IN (SELECT product_id FROM product2)
UNION
SELECT * FROM product2
WHERE product_id NOT IN (SELECT product_id FROM product);
-- 使用集合运算符 （并集与交集的差集）
(SELECT * FROM product UNION ALL SELECT * FROM product2)
EXCEPT
(SELECT * FROM product INTERSECT SELECT * FROM product2);
习题9
题目：
找出每个商店里的衣服类商品的名称及价格等信息. 希望得到如下结果:
+---------+-----------+------------+--------------+--------------+----------------+
| shop_id | shop_name | product_id | product_name | product_type | purchase_price |
+---------+-----------+------------+--------------+--------------+----------------+
| 000A | 东京 | 0001 | T恤衫 | 衣服 | 500 |
| 000A | 东京 | 0003 | 运动T恤 | 衣服 | 2800 |
| 000B | 名古屋 | 0003 | 运动T恤 | 衣服 | 2800 |
| 000C | 大阪 | 0003 | 运动T恤 | 衣服 | 2800 |
| 000D | 福冈 | 0001 | T恤衫 | 衣服 | 500 |
知识点
首先我们回顾一下创建表的知识，通过脚本创建 ShopProduct 表：
-- DDL：创建表
CREATE TABLE ShopProduct
(shop_id CHAR(4) NOT NULL,
shop_name VARCHAR(200) NOT NULL,
product_id CHAR(4) NOT NULL,
quantity INTEGER NOT NULL,
PRIMARY KEY (shop_id, product_id));
-- DML：插入数据
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0001', 30);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0002', 50);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0003', 15);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0002', 30);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0003', 120);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0004', 20);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0006', 10);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0007', 40);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0003', 20);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0004', 50);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0006', 90);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0007', 70);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000D', '福冈', '0001', 100);
COMMIT;
JOIN （连结）是 SQL 中的一种操作，基于这些表之间的共同列将两个或多个表的行组合起来。常见的连结有：
INNER JOIN：只返回两个表中匹配成功的记录。OUTER JOIN: 用于返回至少有一个表中存在的所有记录。LEFT JOIN（或LEFT OUTER JOIN）：返回左表的所有记录和右表中匹配的记录。如果没有匹配的记录，则结果是 NULL。RIGHT JOIN（或RIGHT OUTER JOIN）：返回右表的所有记录和左表中匹配的记录。如果没有匹配的记录，则结果是 NULL。FULL JOIN（或FULL OUTER JOIN）：返回左表和右表中所有的记录。如果没有匹配的记录，则结果是 NULL。CROSS JOIN(笛卡尔积): 在 SQL 中用于生成两个表的笛卡尔积。笛卡尔积是指从两个集合中取出所有可能的组合。在 SQL 中，如果我们有两个表，一个表有 n 行，另一个表有 m 行，那么这两个表的笛卡尔积将有 nm 行。
解题
-- 使用 内连 INNER JOIN 找出两个表中的衣服
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.product_type, P.purchase_price
FROM shopproduct AS SP
INNER JOIN Product AS P ON SP.product_id = P.product_id
WHERE P.product_type = '衣服';
习题10
题目：
分别使用连结两个子查询和不使用子查询的方式, 找出东京商店里, 售价低于 2000 的商品信息,希望得到如下结果。
+---------+-----------+------------+----------+------------+--------------+--------------+------------+----------------+-------------+
| shop_id | shop_name | product_id | quantity | product_id | product_name | product_type | sale_price | purchase_price | regist_date |
+---------+-----------+------------+----------+------------+--------------+--------------+------------+----------------+-------------+
| 000A | 东京 | 0001 | 30 | 0001 | T恤衫 | 衣服 | 1000 | 500 | 2009-09-20 |
| 000A | 东京 | 0002 | 50 | 0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 |
知识点
子查询是在主查询内部嵌套的一个完整的SQL查询。详细知识点见上一个 Task，这里依旧使用内连结进行查询。
解题
-- 使用子查询
SELECT SP.*, P.*
FROM (
SELECT *
FROM shopproduct
WHERE shop_name = '东京'
) AS SP
INNER JOIN (
SELECT *
FROM product
WHERE sale_price < 2000
) AS P
ON SP.product_id = P.product_id;
-- 不使用子查询
SELECT SP.*, P.*
FROM shopproduct AS SP
INNER JOIN product AS P ON SP.product_id = P.product_id
WHERE shop_name = '东京' AND sale_price < 2000;
习题11
题目：
每个商店中, 售价最高的商品的售价分别是多少?
知识点
结合 GROUP BY 子句使用内连结。先连结，再聚合
解题
-- 先 INNER JOIN， 再 GROUP BY
SELECT SP.shop_id, SP.shop_name, MAX(P.sale_price) AS max_price
FROM ShopProduct AS SP
INNER JOIN product AS P ON SP.product_id = P.product_id
GROUP BY SP.shop_id, SP.shop_name;
习题12
题目：
上一题中查询得到了每个商品售价最高的商品, 但并不知道售价最高的商品是哪一个.如何获取每个商店里售价最高的商品的名称和售价?
知识点
使用前面学过的子查询 和 INNER JOIN 实现，例如, 在找出每个商店售价最高商品的价格后, 使用这个价格再与 Product 列进行连结,
不过查询的数据不一定准确，这种做法在价格不唯一时会出现问题。
后续可以通过 窗口函数 实现。
解题
-- 使用子查询
SELECT *
FROM (
SELECT SP.shop_id
,SP.shop_name
,MAX(P.sale_price) AS max_price
FROM ShopProduct AS SP
INNER JOIN product AS P
ON SP.product_id = P.product_id
GROUP BY SP.shop_id,SP.shop_name
) AS MaxPrice
INNER JOIN Product AS P ON MaxPrice.max_price = P.sale_price;
习题13
题目：
`sql
SELECT * FROM shopproduct NATURAL JOIN product;
`
试写出与上述自然连结等价的内连结。
知识点
NATURAL JOIN（自然连结）基于两个表中所有具有相同名称的列进行 JOIN。在 NATURAL JOIN 中，你不需要明确指定连接条件，因为 SQL 会自动找出两个表中名称相同的所有列，并使用这些列作为连接条件。
使用自然连结还可以求出两张表或子查询的公共部分，不过是逐字段，涉及到缺失值(缺失值是不能用等号进行比较)的时候和INTERSECT有些差异。如：
解题
-- 使用 INNER JOIN
SELECT SP.product_id,SP.shop_id,SP.shop_name,SP.quantity,
P.product_name,P.product_type,P.sale_price,P.purchase_price,P.regist_date
FROM shopproduct AS SP
INNER JOIN Product AS P ON SP.product_id = P.product_id;
习题14
题目：
使用内连结求 Product 表和 Product2 表的交集。
知识点
INNER JOIN 返回两个（或多个）表中满足 JOIN 条件的所有行。
解题
SELECT P1.*
FROM Product AS P1
INNER JOIN Product2 AS P2
ON P1.product_id = P2.product_id;
习题15
题目：
使用外连结从ShopProduct表和Product表中找出那些在某个商店库存少于50的商品及对应的商店.希望得到如下结果。
+------------+--------------+------------+---------+-----------+----------+
| product_id | product_name | sale_price | shop_id | shop_name | quantity |
+------------+--------------+------------+---------+-----------+----------+
| 0001 | T恤衫 | 1000 | 000A | 东京 | 30 |
| 0003 | 运动T恤 | 4000 | 000A | 东京 | 15 |
| 0002 | 打孔器 | 500 | 000B | 名古屋 | 30 |
| 0004 | 菜刀 | 3000 | 000B | 名古屋 | 20 |
| 0006 | 叉子 | 500 | 000B | 名古屋 | 10 |
| 0007 | 擦菜板 | 880 | 000B | 名古屋 | 40 |
| 0003 | 运动T恤 | 4000 | 000C | 大阪 | 20 |
| 0005 | 高压锅 | 6800 | NULL | NULL | NULL |
| 0008 | 圆珠笔 | 100 | NULL | NULL | NULL |
+------------+--------------+------------+---------+-----------+----------+
注意高压锅和圆珠笔两种商品在所有商店都无货, 所以也应该包括在内。
知识点
LEFT JOIN 返回左表中的所有行，以及右表中与左表匹配的行。如果右表中没有匹配的行，则返回 NULL 值。
解题
SELECT P.product_id, P.product_name, P.sale_price,
SP.shop_id, SP.shop_name, SP.quantity
FROM Product AS P
LEFT JOIN (SELECT * FROM ShopProduct WHERE quantity < 50) AS SP
ON SP.product_id = P.product_id
ORDER BY ISNULL(SP.shop_id),SP.shop_id ASC;
习题16
题目：
希望对 Product 表中的商品按照售价赋予排名. 一个从集合论出发,使用自左连结的思路是, 对每一种商品,找出售价不低于它的所有商品, 然后对售价不低于它的商品使用 COUNT 函数计数. 例如, 对于价格最高的商品,
知识点
注意 rank 是关键字，使用它作为字段时，需要加 ``,如 `rank`。
解题
SELECT product_id, product_name, sale_price, COUNT(P2_id) AS `rank`
FROM (
SELECT P1.product_id, P1.product_name, P1.sale_price,
P2.product_id AS P2_id, P2.product_name AS P2_name, P2.sale_price AS P2_price
FROM Product AS P1
LEFT JOIN Product AS P2
ON P1.sale_price <= P2.sale_price
) AS X
GROUP BY product_id, product_name, sale_price
ORDER BY `rank`;
习题17
题目：
请按照商品的售价从低到高,对售价进行累计求和[注:这个案例缺少实际意义, 并且由于有两种商品价格相同导致了不必要的复杂度, 但示例数据库的表结构比较简单, 暂时未想出有实际意义的例题]
知识点
涉及到了表的自连接、GROUP BY分组操作、SUM函数的使用以及ORDER BY排序操作等SQL知识点.
- 使用LEFT JOIN进行表的自连接：通过将表与自身进行连接，例如可以获取每个商品与价格不低于它的其他商品的信息。
- 使用GROUP BY进行分组操作：通过GROUP BY子句，例如可以将查询结果按照指定的列进行分组，以便进行聚合操作。
- 使用SUM函数进行求和操作：通过SUM函数，可以对指定列的值进行求和操作，例如可以得到每个商品的累计价格。
- 使用ORDER BY进行排序操作：通过ORDER BY子句，可以按照指定的列对查询结果进行排序，例如可以得到按照销售价格和累计价格排序的结果。
解题
SELECT product_id, product_name, sale_price, SUM(P2_price) AS cum_price
FROM (
SELECT P1.product_id, P1.product_name, P1.sale_price,
P2.product_id AS P2_id, P2.product_name AS P2_name, SUM(P2.sale_price) AS P2_price
FROM Product AS P1
LEFT JOIN Product AS P2
ON (P1.sale_price > P2.sale_price)
OR (P1.sale_price = P2.sale_price AND P1.product_id <= P2.product_id)
GROUP BY P1.product_id, P1.product_name, P1.sale_price, P2.product_id, P2.product_name
) AS X
GROUP BY product_id, product_name, sale_price
ORDER BY sale_price, cum_price;
习题18
题目：
找出 product 和 product2 中售价高于 500 的商品的基本信息。
知识点
可以使用 UNION,注意 UNION 等集合运算符通常都会除去重复的记录
解题
SELECT * FROM product
WHERE sale_price > 500
UNION
SELECT * FROM product2
WHERE sale_price > 500;
习题19
题目：
借助对称差的实现方式, 求product和product2的交集。
知识点
两个集合A,B的对称差是指那些仅属于A或仅属于B的元素构成的集合,首先使用UNION求两个表的并集, 然后使用INTERSECT求两个表的交集, 然后用并集减去交集, 就得到了对称差
解题
SELECT *
FROM (SELECT * FROM product UNION SELECT * FROM product2) AS U
WHERE product_id NOT IN
(SELECT product_id
FROM product
WHERE product_id NOT IN (SELECT product_id FROM product2)
UNION
SELECT product_id
FROM product2
WHERE product_id NOT IN (SELECT product_id FROM product));
习题20
题目：
每类商品中售价最高的商品都在哪些商店有售 ？
知识点
子查询、聚合函数、 GROUP BY，多表查询 INNER JOIN。
解题
SELECT SP.shop_id,SP.shop_name,SP.quantity,SP.product_id,
P.product_name,P.product_type,P.sale_price,
MP.max_sale_price AS 'max_sale_price'
FROM product AS P
INNER JOIN shopproduct AS SP
ON P.product_id = SP.product_id
INNER JOIN (SELECT product_type, max(sale_price) AS max_sale_price
FROM product
GROUP BY product_type) AS MP
ON (P.product_type = MP.product_type) AND (P.sale_price = MP.max_sale_price);
习题21
题目：
分别使用内连结和关联子查询每一类商品中售价最高的商品。
解题
-- 使用内连j结
SELECT p.product_type, p.product_name, p.sale_price
FROM product p
INNER JOIN (
SELECT product_type, MAX(sale_price) AS max_price
FROM product
GROUP BY product_type
) t ON p.product_type = t.product_type AND p.sale_price = t.max_price;
-- 使用关联子查询
SELECT p.product_type, p.product_name, p.sale_price
FROM product p
WHERE p.sale_price = (
SELECT MAX(sale_price)
FROM product
WHERE product_type = p.product_type
);
习题22
题目：
用关联子查询实现：在 product 表中，取出 product_id, product_name, sale_price, 并按照商品的售价从低到高进行排序、对售价进行累计求和。
解题
SELECT p.product_id, p.product_name, p.sale_price, (
SELECT SUM(sale_price)
FROM product
WHERE sale_price <= p.sale_price
) AS cumulative_sum
FROM product p
ORDER BY p.sale_price;
【版权声明】本文为华为云社区用户原创内容，未经允许不得转载，如需转载请自行联系原作者进行授权。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）