Datawhale SQL 04

4.1

UNION ALL 实现不去除重复重复行的并集.

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

4.2

并集-对称差

SELECT * FROM
(SELECT *
  FROM product
 UNION
SELECT * 
  FROM product2) as p
	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 sp.shop_id, sp.shop_name, sp.product_id ,p.product_type
  FROM shopproduct sp
  JOIN product p
    ON sp.product_id=p.product_id
 WHERE sp.product_id in
(SELECT product_id 
   FROM product p1
   JOIN (SELECT product_type,
                MAX(sale_price) as max_price 
           FROM product 
       GROUP BY product_type) p2 
     ON p1.product_type=p2.product_type AND p1.sale_price=p2.max_price);

4.4

 --关联子查询
 SELECT product_type, product_name, sale_price
  FROM product ASP1
 WHERE sale_price = (SELECT max(sale_price)
                       FROM product AS P2
                      WHERE P1.product_type = P2.product_type

--内联结
SELECT  P1.product_id,P1.product_name,P1.product_type,P1.sale_price,P2.max_price
  FROM product AS P1
 INNER JOIN 
   (SELECT product_type,max(sale_price) AS max_price 
      FROM product 
     GROUP BY product_type) AS P2 
    ON P1.product_type = P2.product_type
 WHERE P1.sale_price= p2.max_price;

4.5

SELECT product_id, product_name, sale_price,
	   (SELECT SUM(sale_price) FROM product AS P2
		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,product_id;