天池龙珠计划SQL训练营-Task04学习打卡

天池龙珠计划SQL训练营 task04课程地址

练习题

4.1

找出 product 和 product2 中售价高于 500 的商品的基本信息。
答：

SELECT product_id, product_name
  FROM product where sale_price > 500
 UNION
SELECT product_id, product_name
  FROM product2 where sale_price > 500;

4.2

借助对称差的实现方式, 求product和product2的交集。
答：

SELECT * 
FROM product
WHERE product_id IN (SELECT product_id FROM product2)

4.3

每类商品中售价最高的商品都在哪些商店有售 ？
答：

SELECT
	SP.shop_id,SP.shop_name,P.*
FROM
	product AS P
	INNER JOIN ( 
		SELECT product_type, MAX( sale_price ) as sale_price FROM product GROUP BY product_type 
	) AS tp ON P.product_type = tp.product_type and P.sale_price = tp.sale_price
	left JOIN shop_product as SP ON SP.product_id = P.product_id
;

4.4

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

• 内连接

SELECT
	P.* 
FROM
	product AS P
	INNER JOIN ( 
		SELECT product_type, MAX( sale_price ) AS mp FROM product GROUP BY product_type 
	) AS TP ON P.product_type = tp.product_type AND P.sale_price = TP.mp;

• 子查询

select * from product where (product_type,sale_price) = any(
	select product_type,max(sale_price) from product group by product_type
);

4.5

用关联子查询实现：在product表中，取出 product_id, product_name, sale_price, 并按照商品的售价从低到高进行排序、对售价进行累计求和。
答：

SELECT
	product_id,product_name,sale_price,SUM( pp2 ) AS '累计销售价格' 
FROM
	(
SELECT
		p1.product_id,p1.product_name,p1.sale_price,p2.sale_price AS pp2 
	FROM
		product AS p1
		RIGHT JOIN product AS p2 ON p1.sale_price >= p2.sale_price
		ORDER BY p1.sale_price asc
	) as T
GROUP BY product_id ORDER BY sale_price asc;