练习题
4.1 product 和 product2 中售价高于 500 的商品的基本信息
SELECT * FROM product;
SELECT * FROM product2;
SELECT *
FROM product AS p1
WHERE p1.sale_price > 500
UNION # ALL 9 records
SELECT *
FROM product2 AS p2
WHERE p2.sale_price > 500;
结果如下:
4.2 借助对称差的实现方式, 求product和product2的交集
SELECT *
FROM product AS p1
INNER JOIN product2 AS p2
ON p1.product_id = p2.product_id;
SELECT *
FROM (# a+b
SELECT * -- product_id, product_name, product_type, sale_price, purchase_price, regist_data
FROM product
UNION
SELECT *
FROM product2) AS plus
WHERE plus.product_id NOT IN(
# a-b union b-a
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)
);
结果如下:
4.3 每类商品中售价最高的商品都在哪些商店有售 ?
SELECT *
FROM (SELECT p1.*
FROM product AS p1
WHERE p1.sale_price = (SELECT max(p2.sale_price)
FROM product AS p2
WHERE p1.product_type = p2.product_type)
) AS max_p
LEFT OUTER JOIN shop_product AS sp
ON sp.product_id = max_p.product_id;
4.4 内连结和关联子查询每一类商品中售价最高的商品
-- 1 关联子查询
SELECT p1.*
FROM product AS p1
WHERE p1.sale_price = (SELECT max(p2.sale_price)
FROM product AS p2
WHERE p1.product_type = p2.product_type);
-- 内连结
SELECT p1.*
FROM product AS p1
INNER JOIN(-- 查询每类商品的最高价格
SELECT product_type, max(sale_price) AS m_price
FROM product
GROUP BY product_type) AS p2
ON p1.product_type = p2.product_type
WHERE p1.sale_price = p2.m_price;
结果如下:
4.5 关联子查询实现:在 product 表中,取出 product_id, produc_name, slae_price, 并按照商品的售价从低到高进行排序、对售价进行累计求和。
SELECT product_id
,product_name
,sale_price
,SUM(p2_price) AS sum_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
ORDER BY p1.sale_price, p1.product_id
) AS x
GROUP BY product_id, product_name, sale_price
ORDER BY sale_price, product_id;
结果如下: