第4章集合运算-优快云博客

本文链接：https://blog.youkuaiyun.com/f1220684378/article/details/125902786

本文探讨了SQL查询的四个实例，涉及价格过滤、联合查询、最大售价查找、累计求和以及不重复商品集合。通过Q1和Q2的复杂查询，展示了如何利用子查询和排除操作来提高效率。Q3和Q4聚焦于商品类型与最高售价的关联，而Q5则关注按价格排序的累计销售总额。这些技术适用于数据库管理和数据分析专业人士。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Q1

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

Q2

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

Q3

select sp.shop_id, sp.shop_name, sp.quantity,
        p.product_id, p.product_name, p.product_type, p.sale_price,
        mp.maxp as '该类商品的最大售价' 
from product as p 
inner join shopproduct as sp 
on sp.product_id = p.product_id
inner join (
			select product_type, max(sale_price) as maxp from product 
            group by product_type
            ) as mp
on mp.product_type = p.product_type and p.sale_price = mp.maxp;

Q4

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

Q5

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 '累计求和'
from product as p 
order by sale_price;