天池SQL训练营Day04-集合运算-表的加减法和join等/2021-05-15

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

Day04: 集合运算-表的加减法和join等 Task04：集合运算-表的加减法和join等-天池龙珠计划SQL训练营-天池技术圈-天池技术讨论区 (aliyun.com)

练习

-- 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    (SELECT * 
                FROM product 
                UNION 
                SELECT * 
                FROM product2)A
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 hp.product_id,product_name,shop_name
FROM (SELECT product_id,product_name,p.product_type 
            FROM product    p
            INNER JOIN
            (SELECT product_type,max(sale_price) as sale_price
                FROM product P
                GROUP BY product_type    
             )a
             on p.product_type = a.product_type 
             and p.sale_price = a.sale_price)hp
INNER JOIN shop_product sp
on hp.product_id = sp.product_id
    

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

-- INNER JOIN
SELECT product_id,product_name,p.product_type 
            FROM product    p
            INNER JOIN
            (SELECT product_type,max(sale_price) as sale_price
                FROM product P
                GROUP BY product_type    
             )a
             on p.product_type = a.product_type 
             and p.sale_price = a.sale_price
-- 关联子查询
SELECT p1.product_id, p1.product_name, p1.product_type, p1.sale_price
FROM product AS p1
WHERE p1.sale_price = (SELECT MAX(sale_price) AS 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 product_id, product_name, sale_price,
       (SELECT SUM(sale_price) FROM product AS p2
       -- ①价格更低的 ②价格相等，product_id不大于的（不包括下一行）
        WHERE ((P2.sale_price < P1.sale_price) OR (P2.sale_price = P1.sale_price AND P2.product_id<=P1.product_id))) AS cum_price
FROM product AS p1 
ORDER BY sale_price