wonderful-sql Task04

集合运算

• MySQL 8.1 已经支持交运算 INTERSECT 和表的减法运算符 EXCEPT 了
• MySQL 8.0 目前还不支持全外连结, 不过我们可以对左连结和右连结的结果进行 UNION 来实现全外连结。

练习题

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

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

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

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

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

SELECT
	p.product_type,
	p.product_name,
	sp.shop_id,
	sp.shop_name 
FROM
	product p
	INNER JOIN ( SELECT product_type, MAX( sale_price ) AS max_sale_price FROM product GROUP BY product_type ) sub ON p.product_type = sub.product_type 
	AND p.sale_price = sub.max_sale_price
	INNER JOIN shopproduct sp ON p.product_id = sp.product_id;

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

SELECT
	p.product_type,
	p.product_name,
	p.sale_price 
FROM
	product p
	INNER JOIN ( SELECT product_type, MAX( sale_price ) AS max_sale_price FROM product GROUP BY product_type ) sub ON p.product_type = sub.product_type 
	AND p.sale_price = sub.max_sale_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 );

5 用关联子查询实现：在 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 AS p 
ORDER BY
	p.sale_price;