SQL编程 Task04.集合运算

---

一、表的加减法

学习链接

1.1 第一题

找出 product 和 product2 中售价高于 500 的商品的基本信息。

解：使用UNION ALL求解

SELECT *
FROM product
WHERE sale_price > 500
UNION ALL
SELECT *
FROM product2
WHERE sale_price >500

1.2 第二题

借助对称差的实现方式, 求product和product2的交集。

解：
使用对称差的实现方式求交集，即先求并集，再减去对称差，即可得到交集。

SELECT *
FROM 
(SELECT * FROM product
UNION 
SELECT * FROM product2) 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));

注：每个派生出的表都需要有自己的别名！（代码中的u）

有一种更简单的方法：

SELECT * FROM product WHERE product_id IN (SELECT product_id FROM product2);

1.3 第三题

每类商品中售价最高的商品都在哪些商店有售 ？

解：

SELECT sp.shop_id, sp.shop_name,sp.product_id,p.product_type
	FROM shopproduct sp
INNER JOIN product p
ON sp.product_id=p.product_id
WHERE sp.product_id in
(SELECT product_id
FROM product p1
INNER JOIN (SELECT product_type,
			MAX(sale_price) as max_price
			FROM product 
			GROUP BY product_type ) p2
			ON p1.product_type = p2.product_type AND p1.sale_price=p2.max_price);

1.4 第四题

分别使用内连结和关联子查询每一类商品中售价最高的商品。

解：
关联子查询：

SELECT product_type,product_name,sale_price
	FROM product AS P1
WHERE sale_price = (SELECT MAX(sale_price)
											FROM product AS P2
										WHERE P1.product_type = P2.product_type)

内连结：

SELECT P1.product_id,P1.product_name,P1.product_type,P1.sale_price,P2.max_price
	FROM product AS P1
INNER JOIN
	(SELECT product_type,max(sale_price) AS max_price
			FROM product
		GROUP BY product_type) AS P2
				ON P1.product_type = P2.product_type
WHERE P1.sale_price = P2.max_price

1.5 第五题

用关联子查询实现：在 product 表中，取出 product_id, product_name, sale_price, 并按照商品的售价从低到高进行排序、对售价进行累计求和。

SELECT product_id,product_name,sale_price,
			(SELECT SUM(sale_price) FROM product AS P2
			WHERE P1.sale_price > P2.sale_price
			OR (P1.sale_price = P2.sale_price AND P1.product_id >= P2.product_id)) AS COM_PRICE
FROM product AS P1
ORDER BY sale_price;