（课后练习题）Task04：集合运算-表的加减法和join等-天池龙珠计划SQL训练营

本笔记为阿里云天池龙珠计划SQL训练营的学习内容，链接为：https://tianchi.aliyun.com/specials/promotion/aicampsql

目录

练习题

4.1

4.2

4.3

4.4

4.5

---

原文并没有直接给出product2的数据，但我们可以从以下两张图推断出： 

product2的建表代码为：

INSERT INTO product2 VALUES('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');
INSERT INTO product2 VALUES('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO product2 VALUES('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO product2 VALUES('0009', '手套', '衣服', 800, 500, NULL);
INSERT INTO product2 VALUES('0010', '水壶', '厨房用具', 2000, 1700, '2009-09-20');
COMMIT;

练习题

4.1

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

解：

先product和product2求并集，然后在并集中查找，其实倒过来也是一样的，但实际上sql对于不同表的操作都是先SELECT再UNION：

确实, 对于同一个表的两个不同的筛选结果集, 使用 UNION 对两个结果集取并集, 和把两个子查询的筛选条件用 OR 谓词连接, 会得到相同的结果, 但倘若要将两个不同的表中的结果合并在一起, 就不得不使用 UNION 了.

而且, 即便是对于同一张表, 有时也会出于查询效率方面的因素来使用 UNION.

代码如下：

SELECT *
  FROM product
  WHERE sale_price > 500
 UNION
SELECT *
  FROM product2
  WHERE sale_price >500;

运行结果：

 

 

4.2

借助对称差的实现方式, 求product和product2的交集。

解：本来想着怎么用UNION，后来一想哪用这么复杂：

SELECT * 
  FROM product
 WHERE product_id NOT IN (SELECT product_id FROM product2)

运行结果：

 

 

4.3

每类商品中售价最高的商品都在哪些商店有售 ？

解：先来看看商店商品表shopproduct

在此，我们创建一张新表shopproduct显示出哪些商店销售哪些商品。

-- DDL ：创建表
DROP TABLE IF EXISTS shopproduct;
CREATE TABLE shopproduct
(  shop_id CHAR(4)     NOT NULL,
 shop_name VARCHAR(200) NOT NULL,
product_id CHAR(4)      NOT NULL,
  quantity INTEGER      NOT NULL,
PRIMARY KEY (shop_id, product_id) -- 指定主键
);
-- DML ：插入数据
START TRANSACTION; -- 开始事务
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0001', 30);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0002', 50);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0003', 15);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0002', 30);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0003', 120);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0004', 20);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0006', 10);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0007', 40);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0003', 20);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0004', 50);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0006', 90);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0007', 70);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000D', '福冈', '0001', 100);
COMMIT; -- 提交事务
SELECT * FROM shopproduct;
+---------+-----------+------------+----------+
| shop_id | shop_name | product_id | quantity |
+---------+-----------+------------+----------+
| 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 |
| 000C    | 大阪      | 0006       |       90 |
| 000C    | 大阪      | 0007       |       70 |
| 000D    | 福冈      | 0001       |      100 |
+---------+-----------+------------+----------+
13 rows in set (0.00 sec)

两张不同类型的表，就要使用内连接：

SELECT PP.product_type,PP.product_name,PP.product_id
      ,PP.max_price,PS.shop_id,PS.shop_name
FROM (SELECT P.product_type,P.product_name,P.product_id
      ,MAX(P.sale_price) AS max_price
  FROM product AS P
 GROUP BY P.product_type) AS PP
 INNER JOIN shopproduct AS PS
 ON PP.product_id = PS.product_id

运行结果：

 

 

4.4

分别使用内连结和关联子查询每一类商品中售价最高的商品。

解：虽然我感觉有点多此一举。。。。

SELECT  P1.product_id
       ,P1.product_name
       ,P1.product_type
       ,P1.sale_price
       ,P2.max_price
  FROM product AS P1
 INNER JOIN 
   (SELECT product_type,MAX(sale_price) AS max_price 
      FROM product 
     GROUP BY product_type) AS P2 
    ON P1.product_type = P2.product_type
 WHERE P1.sale_price = P2.max_price;

 

 

4.5

用关联子查询实现：在product表中，取出 product_id, produc_name, slae_price, 并按照商品的售价从低到高进行排序、对售价进行累计求和。

解：不太理解题意，这个累计是销售量乘售价吗？

SELECT  P1.product_id, P1.product_name, P1.sale_price,
 P2.sum_quantity * P1.sale_price AS sum_sale_price
  FROM product AS P1
 LEFT OUTER JOIN (SELECT product_id,SUM(quantity) AS sum_quantity
	FROM shopproduct
    GROUP BY product_id) AS P2
    ON P1.product_id = P2.product_id
 ORDER BY P1.sale_price;