#4.1 找出 product 和 product2 中售价高于 500 的商品的基本信息。
SELECT * FROM product
WHERE sale_price > 500
UNION
SELECT * FROM product2
WHERE sale_price > 500;
#4.2 借助对称差的实现方式, 求product和product2的交集。
SELECT * FROM
(SELECT * FROM product
UNION
SELECT * FROM product2) AS p1
WHERE product_id NOT IN
(SELECT product_id FROM
(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)) AS p2);
#4.3 每类商品中售价最高的商品都在哪些商店有售 ?
#关键点:内连结会丢弃两张表中不满足 ON 条件的行。
#step1 找出售价最高的商品
SELECT * FROM product AS p
JOIN (SELECT product_type, MAX(sale_price) AS max_price
FROM product
GROUP BY product_type) AS p2
ON p.product_type = p2.product_type
WHERE p.sale_price = max_price;
#step2 嵌套进行连结(包含step1)
SELECT shop_id, shop_name, product_name, sale_price, p3.product_id
FROM shop_product
JOIN
(SELECT product_id, product_name, sale_price
FROM product AS p
JOIN (SELECT product_type, MAX(sale_price) AS max_price
FROM product
GROUP BY product_type) AS p2
ON p.product_type = p2.product_type
WHERE p.sale_price = max_price) AS p3
ON shop_product.product_id = p3.product_id;
#4.4 分别使用内连结和关联子查询每一类商品中售价最高的商品。
#法一、内连结
SELECT p.product_id, p.product_name, p.product_type, p.sale_price
FROM product AS p
JOIN (SELECT product_type, MAX(sale_price) AS max_price
FROM product
GROUP BY product_type) AS p2
ON p.product_type = p2.product_type
WHERE p.sale_price = max_price;
#法二、关联子查询
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 p2
WHERE p.product_type = p2.product_type
GROUP BY product_type);
#4.5 用关联子查询实现:在 product 表中,取出 product_id, product_name, sale_price, 并按照商品的售价从低到高进行排序、对售价进行累计求和。
SELECT p.product_id, p.product_name, p.product_type, p.sale_price,
(SELECT SUM(sale_price) FROM product AS p2
WHERE p.sale_price > p2.sale_price
OR (p.sale_price = p2.sale_price AND p.product_id >= p2.product_id)) AS 累计
FROM product AS p
ORDER BY sale_price;