SQL训练营_task4

练习题

4.1

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

select * from
product where sale_price>500
union
select * from 
product2 where sale_price>500;

4.2

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

select * from
(select * from product 
union
select * from product2) a
where product_id
not in
(select product_id 
from(
(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)))b);

4.3

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

select 
p.product_id,
p.product_name,
p.product_type,
mp.max_price,
sp.shop_id,
sp.shop_name
from product p
INNER JOIN(
select
	product_type,
	max(sale_price) as max_price
from product
GROUP BY product_type) mp
on p.product_type = mp.product_type
	and p.sale_price = mp.max_price
INNER JOIN shop_product sp
on sp.product_id=p.product_id
ORDER BY product_id;

-- 查询结果中缺失厨房用具一类，原因为售价最高的厨房用具类商品ID为0005，而shop_produt表中没有0005的数据，所以内链接出来最后少一类，下面改为左链接

select 
p.product_id,
p.product_name,
p.product_type,
mp.max_price,
sp.shop_id,
sp.shop_name
from product p
INNER JOIN(
select
	product_type,
	max(sale_price) as max_price
from product
GROUP BY product_type) mp
on p.product_type = mp.product_type
	and p.sale_price = mp.max_price
left JOIN shop_product sp
on sp.product_id=p.product_id
ORDER BY product_id;

内链接结果： 

左链接结果： 

 

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, produc_name, slae_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 '累计销售价格'
FROM product AS P1 
ORDER BY sale_price;

SELECT P1.product_id ,P1.product_name ,P2.sale_price ,SUM(P2.sale_price) AS '累计销售价格'
	FROM Product AS P1 
	LEFT JOIN Prouduct AS P2
	ON P1.sale_price >= P2.sale_price
	AND P1.product_id != P2.product_id
	GROUP BY P1.product_id ,P1.product_type
	ORDER BY sale_price DESC