作业
集合运算的内容有点多,而且和之前的比起来难度更大了。这方面的知识还没有完全参透,还需要多花点时间。
作业1
找出 product 和 product2 中售价高于 500 的商品的基本信息。
代码:
SELECT * FROM `product` WHERE sale_price >= 500
UNION
SELECT * FROM PRODUCT2 WHERE sale_price >=500;
作业2
借助对称差的实现方式, 求product和product2的交集。
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);
作业3
每类商品中售价最高的商品都在哪些商店有售 ?
代码:(这题还没能完全理解)
select s.product_id, shop_name, product_type
from (
select product_id, m.product_type
from (select * from product union select * from product2) all_product
join (
select max(sale_price) max, product_type
from (select * from product union select * from product2) all_product
group by product_type
) m
on all_product.product_type = m.product_type
WHERE sale_price = max
) id
left join ShopProduct s
on id.product_id = s.product_id;
作业4
分别使用内连结和关联子查询每一类商品中售价最高的商品。
内连结:
select *
from (select * from product union select * from product2) p1
where sale_price = (
select max(sale_price)
from (select * from product union select * from product2) p2
where p2.product_type = p1.product_type
group by product_type
);
关联子:
select product_id
from (select * from product union select * from product2) all_product
join (select max(sale_price) max, product_type
from (select * from product union select * from product2) all_product
group by product_type) m
on all_product.product_type = m.product_type
where sale_price = max;
作业5
用关联子查询实现:在 product 表中,取出 product_id, produc_name, slae_price, 并按照商品的售价从低到高进行排序、对售价进行累计求和。
代码:
SELECT product_id, product_name, sale_price,( SELECT SUM( sale_price ) FROM product p2 WHERE p2.sale_price<=p.sale_price ) s_sale
FROM product p
ORDER BY sale_price;
参考资料:
【1】Datawhale开源资料_集合运算