ch04: 集合运算

B2M

已于 2022-06-26 14:02:17 修改

阅读量186

点赞数

文章标签： sql

于 2022-06-23 02:40:03 首次发布

本文链接：https://blog.csdn.net/weixin_43069874/article/details/125419644

版权

练习题（请给出代码、包含代码及代码执行结果的截图）

4.1

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

SELECT *
FROM product
WHERE sals_price > 500
UNION
SELECT *
FROM product2
WHERE sals_price > 500

4.2

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

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

4.3

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

SELECT SP.shop_id, SP.shop_name, SP.product_id 
FROM shop_product AS SP
WHERE SP.product_id IN
(SELECT p1.product_id FROM product AS p1
INNER JOIN 
(SELECT product_type, MAX(sale_price) AS max_sale_price
FROM product
GROUP BY product_type) AS p2
ON p1.product_type = p2.product_type
AND p1.sale_price = p2.max_sale_price);

4.4

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

内连接

SELECT p1.product_id, p1.product_name, p1.product_type, p1.sale_price, p2.max_sale_price 
FROM product AS p1
INNER JOIN 
	(SELECT product_type, MAX(sale_price) AS max_sale_price
	FROM product
	GROUP BY product_type) AS p2
ON p1.product_type = p2.product_type
AND p1.sale_price = p2.max_sale_price;

关联子查询

SELECT p1.product_id, p1.product_name, p1.product_type, p1.sale_price
FROM product AS p1
WHERE p1.sale_price = (SELECT MAX(sale_price) AS max_sale_price
											 FROM product AS p2
											 WHERE p1.product_type = p2.product_type
											 GROUP BY product_type);

4.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
		AND P1.product_id <= P2.product_id) AS cum_price
FROM product AS P1 
ORDER BY sale_price;