本笔记为阿里云天池龙珠计划SQL训练营的学习内容,链接为:https://tianchi.aliyun.com/specials/promotion/aicampsql;
4.1
找出 product 和 product2 中售价高于 500 的商品的基本信息。
SELECT P1.product_id, P1.product_name, P1.product_type,
P1.sale_price, P1.purchase_price, P1.regist_date
FROM product AS P1
INNER JOIN (SELECT product_id, product_name, product_type,
sale_price, purchase_price, regist_date
FROM product2) AS P2
WHERE P1.sale_price > 500;
不太对劲、、浅改一下:
SELECT * FROM product WHERE sale_price > 500
UNION
SELECT * FROM product2 WHERE sale_price > 500;
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 SP.shop_id, SP.shop_name, SP.quantity, SP.product_id
FROM shopproduct AS SP
INNER JOIN product AS P ON SP.product_id = P.product_id
WHERE (product_type, sale_price)
IN (SELECT product_type, MAX(sale_price)
FROM product
GROUP BY product_type)
ORDER BY product_type;
然而不知道是什么商品。反过来再写一次:
SELECT product_type, product_name, shop_name, sale_price
FROM product AS P
INNER JOIN shopproduct AS SP ON P.product_id = SP.product_id
WHERE (product_type, sale_price)
IN (SELECT product_type, MAX(sale_price)
FROM product
GROUP BY product_type)
ORDER BY product_type;
4.4
分别使用内连结和关联子查询每一类商品中售价最高的商品。
内联结:
SELECT P1.product_type, P1.product_name, P1.sale_price
FROM product AS P1
INNER JOIN (SELECT product_type, MAX(sale_price) AS sale_price
FROM product
GROUP BY product_type) AS P2
ON P1.sale_price = P2.sale_price
AND P1.product_type = P2.product_type
ORDER BY product_type;
关联子查询:
SELECT product_type, product_name, sale_price
FROM product
WHERE (product_type, sale_price)
IN (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,
(SELECT SUM(sale_price)
FROM product AS p2
WHERE p1.sale_price >= p2.sale_price) AS total_sale_price
FROM product AS p1
ORDER BY sale_price;