阿里云天池SQL训练营Task04

本笔记为阿里云天池龙珠计划SQL训练营的学习内容，链接为：https://tianchi.aliyun.com/specials/promotion/aicampsql

--------------------------------------------------------------------------------------------------------------------------------

练习题

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

4.3

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

 select shop_id,shop_name,
t2.product_id,t2.product_name,
t2.product_type,t2.max_sale_price
 from shop_product as t1
 inner join
 (select max(sale_price) as max_sale_price,
product_id,product_name,product_type
 from product 
 group by product_type) t2 
 on t1.product_id =t2.product_id

4.4

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

关联子查询
 select product_type,product_name,sale_price
 from product 
 where (sale_price,product_type) in
 (select max(sale_price) ,product_type from product group by product_type)

内连接
 select t1.product_type,t1.product_name,t1.sale_price
 from product as t1
 inner join
 (select product_type,max(sale_price) as sale_price 
 from product 
 group by product_type ) as t2
 on t1.product_type=t2.product_type
 and t1.sale_price =t2.sale_price

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  
			OR 
				p1.sale_price = p2.sale_price 
				AND p1.product_id <= p2.product_id ) AS '累计销售价格'
FROM product AS p1
ORDER BY sale_price,product_id desc;