练习题
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) a
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)))b);
4.3
每类商品中售价最高的商品都在哪些商店有售 ?
select
p.product_id,
p.product_name,
p.product_type,
mp.max_price,
sp.shop_id,
sp.shop_name
from product p
INNER JOIN(
select
product_type,
max(sale_price) as max_price
from product
GROUP BY product_type) mp
on p.product_type = mp.product_type
and p.sale_price = mp.max_price
INNER JOIN shop_product sp
on sp.product_id=p.product_id
ORDER BY product_id;
-- 查询结果中缺失厨房用具一类,原因为售价最高的厨房用具类商品ID为0005,而shop_produt表中没有0005的数据,所以内链接出来最后少一类,下面改为左链接
select
p.product_id,
p.product_name,
p.product_type,
mp.max_price,
sp.shop_id,
sp.shop_name
from product p
INNER JOIN(
select
product_type,
max(sale_price) as max_price
from product
GROUP BY product_type) mp
on p.product_type = mp.product_type
and p.sale_price = mp.max_price
left JOIN shop_product sp
on sp.product_id=p.product_id
ORDER BY product_id;
内链接结果:
左链接结果:
4.4
分别使用内连结和关联子查询每一类商品中售价最高的商品。
SELECT p1.product_id, p1.product_name, p1.product_type, p1.sale_price, p2.max_sale_price
FROM product AS p1
INNER JOIN
(SELECT product_type, MAX(sale_price) AS max_sale_price
FROM product
GROUP BY product_type) AS p2
ON p1.product_type = p2.product_type
AND p1.sale_price = p2.max_sale_price;
SELECT p1.product_id, p1.product_name, p1.product_type, p1.sale_price
FROM product AS p1
WHERE p1.sale_price = (SELECT MAX(sale_price) AS max_sale_price
FROM product AS p2
WHERE p1.product_type = p2.product_type
GROUP BY product_type);
4.5
用关联子查询实现:在product
表中,取出 product_id, produc_name, slae_price, 并按照商品的售价从低到高进行排序、对售价进行累计求和。
SELECT product_id, product_name, sale_price,
(SELECT SUM(sale_price) FROM product AS P2
WHERE P1.sale_price >= P2.sale_price
AND P1.product_id <= P2.product_id) AS '累计销售价格'
FROM product AS P1
ORDER BY sale_price;
SELECT P1.product_id ,P1.product_name ,P2.sale_price ,SUM(P2.sale_price) AS '累计销售价格'
FROM Product AS P1
LEFT JOIN Prouduct AS P2
ON P1.sale_price >= P2.sale_price
AND P1.product_id != P2.product_id
GROUP BY P1.product_id ,P1.product_type
ORDER BY sale_price DESC