第四章 集合运算

练习

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 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 p.product_id ,product_type ,product_name ,sale_price ,shop_name
from product p 
left outer join shopproduct 
on p.product_id =shopproduct.product_id 
WHERE 1=(select count(DISTINCT sale_price)
          from product p2 
          where p.product_type =p2.product_type 
          and p.sale_price >=p2.sale_price )

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

SELECT p.product_id ,p.product_type ,product_name ,sale_price ,shop_name
from product p 
left join shopproduct 
on p.product_id =shopproduct.product_id 
inner join 
(select product_type , max(sale_price) as da
from product p3 
group by product_type )as pda
on p.product_type =pda.product_type 
and p.sale_price =pda.da

4.5用关联子查询实现：在 product 表中，取出 product_id, product_name, sale_price, 并按照商品的售价从低到高进行排序、对售价进行累计求和。

SELECT	product_id, product_name, sale_price
       ,(select SUM(P2.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)) )       
  FROM Product AS P1 
 ORDER BY sale_price asc, product_id asc ;