天池龙珠计划SQL训练营4

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的交集。

因为在MySQL 8.0 不支持交运算INTERSECT，由以下图可了解到并集减去对称差可得到交集,
可以先product - product2并product2 - product的到对称差，再product并product2 - 对称差就可以得到交集。

select * from 
(select * from product 
union 
select * from product2) as p1 
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));

运行结果

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

select sp.shop_id,
sp.shop_name,
sp.quantity,
p.product_id,
p.product_name,
p.product_type,
mp.max_price 
from product as p 
inner join 
shop_product as sp 
on p.product_id = sp.product_id 
inner join 
(select product_type,max(sale_price) as max_price from 
product as p1 
group by product_type) as mp 
on mp.product_type = p.product_type and p.sale_price = mp.max_price;

运行结果

思路：先将product表和shop_product表用inner join 链接起来，再将每种商品最高价格的商品找出来连接就可以得到每种商品最高价格在哪个商店有卖。

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

select p.product_id,
p.product_name,
p.product_type,
mp.max_price,
p.regist_date 
from product as p 
inner join 
(select product_type,max(sale_price) as max_price from product 
group by product_type) as mp
on p.product_type = mp.product_type and p.sale_price = mp.max_price;

运行结果

关联子查询：

select p1.product_id,
p1.product_name,
p1.product_type,
p1.sale_price as max_price,
p1.regist_date 
from product as p1
where sale_price = (select 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 p.product_id, p.product_name, p.product_type, p.sale_price,
(select sum(sale_price) from product as p1 
where p.sale_price > p1.sale_price 
or (p.sale_price = p1.sale_price and p.product_id >= p1.product_id)) as 'c_s'
from product as p 
order by sale_price;

运行结果