天池SQL训练营task04练习

/*4.1
找出 product 和 product2 中售价高于 500 的商品的基本信息。*/
SELECT * FROM product WHERE sale_price > 500
UNION ALL
SELECT * FROM product2 WHERE sale_price > 500;

/*4.2
借助对称差的实现方式, 求product和product2的交集。*/
SELECT * FROM 
	(SELECT * FROM product 
		UNION 
	 SELECT * FROM product2) 
	as u
	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)
			);
	
SELECT * FROM product WHERE product_id IN (SELECT product_id FROM product2);
 
/*4.3 每类商品中售价最高的商品都在哪些商店有售 ？*/
SELECT sp.shop_id, sp.shop_name, sp.quantity,
        p.product_id, p.product_name, p.product_type, p.sale_price,
        mp.maxp AS '该类商品的最大售价' 
FROM product AS p 
INNER JOIN shopproduct AS sp 
ON sp.product_id = p.product_id
INNER JOIN (
			SELECT product_type, MAX(sale_price) AS maxp FROM product 
            GROUP BY product_type
            ) AS mp
ON mp.product_type = p.product_type AND p.sale_price = mp.maxp;
 
 /*4.4 分别使用内连结和关联子查询每一类商品中售价最高的商品。*/
 -- 内连接
SELECT p.product_id, p.product_name, p.product_type, p.sale_price
FROM product AS p 
INNER JOIN(
			SELECT product_type, MAX(sale_price) AS maxp FROM product 
            GROUP BY product_type
            ) AS mp
ON mp.product_type = p.product_type AND p.sale_price = mp.maxp;

-- 关联子查询
SELECT p.product_id, 
			 p.product_name, 
			 p.product_type, 
			 p.sale_price 
			 FROM product AS p
WHERE sale_price = 
									(SELECT MAX(sale_price) 
										FROM product AS p1 
										WHERE p.product_id = p1.product_id 
										GROUP BY product_type);
										
/*4.5
用关联子查询实现：在product表中，取出 product_id, produc_name, slae_price, 并按照商品的售价从低到高进行排序、对售价进行累计求和。*/
SELECT p.product_id, p.product_name, p.product_type, p.sale_price,
	(SELECT SUM(sale_price) FROM product AS p1
	WHERE p.sale_price > p1.sale_price
	OR (p.sale_price = p1.sale_price AND p.product_id >= p1.product_id)
	) AS '累计求和'
FROM product AS p 
ORDER BY sale_price;