Task04：集合运算

创建ShopProduct 商店商品表代码：

CREATE TABLE ShopProduct
(shop_id CHAR(4) NOT NULL,
shop_name VARCHAR(20) NOT NULL,
product_id CHAR(4) NOT NULL,
quantity INTEGER NOT NULL
);
INSERT INTO ShopProduct VALUES('000A','东京','0001',30),
('000A','东京','0002',50),
('000A','东京','0003',15),
('000B','名古屋','0002',30),
('000B','名古屋','0003',120),
('000B','名古屋','0004',20),
('000B','名古屋','0006',10),
('000B','名古屋','0007',40),
('000C','大阪','0003',20),
('000C','大阪','0004',50);

练习

4.1 找出 product 和 product2 中售价高于 500 的商品的基本信息。

创建product2代码如下

CREATE TABLE product2 (
  product_id char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  product_name varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  product_type varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  sale_price int DEFAULT NULL,
  purchase_price int DEFAULT NULL,
  regist_date date DEFAULT NULL,
  PRIMARY KEY (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

insert  into product2(product_id,product_name,product_type,sale_price,purchase_price,regist_date) 
values ('0001','T恤','衣服',1000,500,'2009-09-20'),
('0002','打孔器','办公用品',500,320,'2009-09-11'),
('0003','运动T恤','衣服',4000,2800,NULL),
('0009','手套','衣服',800,500,NULL),
('0010','水壶','厨房用具',2000,1700,'2009-09-20');

解题代码：

SELECT * FROM product WHERE sale_price > 500
UNION ALL
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
left JOIN shopproduct 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