本笔记为阿里云天池龙珠计划SQL训练营的学习内容
练习题
4.1
找出 product 和 product2 中售价高于 500 的商品的基本信息。
SELECT * FROM product WHERE sale_price>500 UNION
SELECT * FROM product2 WHERE sale_price>500 UNION;
4.2
借助对称差的实现方式, 求product和product2的交集。
SELECT * FROM product WHERE product_id NOT IN (SELECT product_id FROM product2) UNION SELECT * FROM product2 WHERE product_id NOT IN (SELECT product_id FROM product);
4.3
每类商品中售价最高的商品都在哪些商店有售 ？
SELECT P.product_name
,SP.shop_name
,MAX(P.sale_price) AS max_price
FROM shopproduct AS SP
INNER JOIN product AS P
ON SP.product_id = P.product_id
GROUP BY P.product_name,SP.shop_name;
4.4
分别使用内连结和关联子查询每一类商品中售价最高的商品。
SELECT P1.product_id
,P1.product_name
,P1.product_type
,P1.sale_price
,MAX(P2.sale_price) AS max_price
FROM product AS P1
INNER JOIN product AS P2
ON P1.product_type=P2.product_type
WHERE P1.sale_price = max_price
GROUP BY P1.product_id,P1.product_name,P1.product_type,P1.sale_price,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
用关联子查询实现：在product表中，取出 product_id, produc_name, slae_price, 并按照商品的售价从低到高进行排序、对售价进行累计求和。
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;
