天池训练营-Mysql-task04

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

SELECT * FROM product WHERE sale_price>500
UNION SELECT * FROM product2 WHERE sale_price>500;

4.2

SELECT * FROM product	AS p1
WHERE p1.product_id NOT IN (SELECT p2.product_id FROM product2
UNION SELECT * FROM product2 AS p2
WHERE product_id NOT IN (SELECT product_id FROM product2);

或者

SELECT * FROM product	AS p1
UNION ALL SELECT * FROM product2 AS p2
NOT IN (SELECT * FROM p1 UNION SELECT * FROM p2);

4.3

SELECT sp.shop_id,sp.shop_name,max.product_id,max.product_name,max.product_type
FROM shopproduct AS sp 
INNER JOIN 
(SELECT p1.product_id, p1.product_name, p1.product_type, p1.sale_price, p2.max_sale_price 
FROM product AS p1
INNER JOIN 
	(SELECT product_type, MAX(sale_price) AS max_sale_price
	FROM product
	GROUP BY product_type) AS p2
ON p1.product_type = p2.product_type
AND p1.sale_price = p2.max_sale_price) AS max
ON sp.product_id=max.product_id;

4.4
内连接方式

SELECT p1.product_id, p1.product_name, p1.product_type, p1.sale_price, p2.max_sale_price 
FROM product AS p1
INNER JOIN 
	(SELECT product_type, MAX(sale_price) AS max_sale_price
	FROM product
	GROUP BY product_type) AS p2
ON p1.product_type = p2.product_type
AND p1.sale_price = p2.max_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

SELECT	product_id, product_name, sale_price
       ,SUM(P2_price) AS cum_price 
  FROM
        (SELECT  P1.product_id, P1.product_name, P1.sale_price
                ,P2.product_id AS P2_id
                ,P2.product_name AS P2_name
                ,P2.sale_price AS P2_price 
           FROM product AS P1 
           LEFT OUTER JOIN product AS P2 
             ON ((P1.sale_price > P2.sale_price)
             OR (P1.sale_price = P2.sale_price 
            AND P1.product_id<=P2.product_id))
	      ORDER BY P1.sale_price,P1.product_id) AS X
 GROUP BY product_id, product_name, sale_price
 ORDER BY sale_price,cum_price;