Task04：集合运算
教程地址
https://github.com/datawhalechina/wonderful-sql
https://gitee.com/datawhalechina/wonderful-sql
1. 表的加减法
表的加减法就是基于集合运算，想想交集、并集和差集
一般来说是，union、intersect、except
1.1 union
1.1.1 优先展示
union就是并集，将相同的列连接起来，默认是去重的
我之前在笔试的时候见过一个类型的，是说要优先展示符合某一个条件的数据
例如我们选择优先展示售价小于 800 的商品
mysql> SELECT product_name,sale_price
-> FROM product
-> WHERE sale_price<800
->
-> UNION
->
-> SELECT product_name,sale_price
-> FROM product
-> WHERE sale_price>=800
-> ;
+--------------+------------+
| product_name | sale_price |
+--------------+------------+
| 打孔器 | 500 |
| 叉子 | 500 |
| T恤 | 1000 |
| 运动T恤 | 4000 |
| 菜刀 | 3000 |
| 高压锅 | 6800 |
| 擦菜板 | 880 |
| 圆珠笔 | 5000 |
+--------------+------------+
8 rows in set (0.00 sec)
当然也可以合并来自不同表的相同列
1.1.2 合并结果
使用 union，其实也类似于 or
即合并两个筛选条件所得到的结果
1.1.3 保留重复
上文说过默认是去重的，如果我们用 union all，那就是不去重的
mysql> SELECT product_id, product_name
-> FROM product
-> UNION ALL
-> SELECT product_id, product_name
-> FROM product2;
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 0001 | T恤 |
| 0002 | 打孔器 |
| 0003 | 运动T恤 |
| 0004 | 菜刀 |
| 0005 | 高压锅 |
| 0006 | 叉子 |
| 0007 | 擦菜板 |
| 0008 | 圆珠笔 |
| 0001 | T恤 |
| 0002 | 打孔器 |
| 0003 | 运动T恤 |
| 0009 | 手套 |
| 0010 | 水壶 |
+------------+--------------+
13 rows in set (0.00 sec)
mysql>
1.1.4 类型转换
我们在合并数据时，同一列的数据类型一定是相同的
但是它们来自不同的数据源，所以可能存在隐式转换
需要注意的是 hive中进行join关联时，关联列要避免使用隐式数据类型转换，否则容易导致数据倾斜
1.2 intersect
intersect 其实就是求交集，但是 mysql 目前是不支持的
我们需要用内连接来实现这个操作
mysql> SELECT p1.product_id, p1.product_name
-> FROM product AS p1
-> INNER JOIN product2 AS p2
-> ON p1.product_id=p2.product_id
-> ;
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 0001 | T恤 |
| 0002 | 打孔器 |
| 0003 | 运动T恤 |
+------------+--------------+
3 rows in set (0.00 sec)
以 product_id 为关键字，取相同的部分
1.3 except
这个差集，mysql 目前也是不支持的
我们也需要用其他方式实现
例如用 not in，选出不在子查询当中的记录
也就是，外查询减去子查询
mysql> SELECT *
-> FROM product
-> WHERE product_id NOT IN (
-> SELECT product_id FROM product2)
-> ;
+------------+--------------+--------------+------------+----------------+-------------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date |
+------------+--------------+--------------+------------+----------------+-------------+
| 0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20 |
| 0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-01-15 |
| 0006 | 叉子 | 厨房用具 | 500 | NULL | 2009-09-20 |
| 0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28 |
| 0008 | 圆珠笔 | 办公用品 | 5000 | NULL | 2009-11-11 |
+------------+--------------+--------------+------------+----------------+-------------+
5 rows in set (0.04 sec)
1.4 对称差
这个就神奇一点，指的是两个集合各自减去交集的部分
同样，mysql 并不支持直接的语法，我们需要用其他办法实现
直觉一点的说，我们将两个集合各自减去交集，再合并就行
但是这样需要先求交集，在这里我们可以直接减去另一个集合
相当于减去了交集，实现的效果基本是一致的
mysql> 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)
-> ;
+------------+--------------+--------------+------------+----------------+-------------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date |
+------------+--------------+--------------+------------+----------------+-------------+
| 0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20 |
| 0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-01-15 |
| 0006 | 叉子 | 厨房用具 | 500 | NULL | 2009-09-20 |
| 0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28 |
| 0008 | 圆珠笔 | 办公用品 | 5000 | NULL | 2009-11-11 |
| 0009 | 手套 | 衣服 | 800 | 500 | NULL |
| 0010 | 水壶 | 厨房用具 | 2000 | 1700 | 2009-09-20 |
+------------+--------------+--------------+------------+----------------+-------------+
7 rows in set (0.01 sec)
2. 连结
2.1 内连结
挺神奇的，我看其他地方不都是，连接，我也习惯称之为连接
内连接就是根据关键字，取交集部分，合并两个表的列
2.1.1 普通连接
mysql> SELECT SP.shop_id
-> ,SP.shop_name
-> ,SP.product_id
-> ,P.product_name
-> ,P.product_type
-> ,P.sale_price
-> ,SP.quantity
-> FROM shopproduct AS SP
-> INNER JOIN product AS P
-> ON SP.product_id = P.product_id
-> ;
+---------+-----------+------------+--------------+--------------+------------+----------+
| shop_id | shop_name | product_id | product_name | product_type | sale_price | quantity |
+---------+-----------+------------+--------------+--------------+------------+----------+
| 000A | 东京 | 0001 | T恤 | 衣服 | 1000 | 30 |
| 000A | 东京 | 0002 | 打孔器 | 办公用品 | 500 | 50 |
| 000A | 东京 | 0003 | 运动T恤 | 衣服 | 4000 | 15 |
| 000B | 名古屋 | 0002 | 打孔器 | 办公用品 | 500 | 30 |
| 000B | 名古屋 | 0003 | 运动T恤 | 衣服 | 4000 | 120 |
| 000B | 名古屋 | 0004 | 菜刀 | 厨房用具 | 3000 | 20 |
| 000B | 名古屋 | 0006 | 叉子 | 厨房用具 | 500 | 10 |
| 000B | 名古屋 | 0007 | 擦菜板 | 厨房用具 | 880 | 40 |
| 000C | 大阪 | 0003 | 运动T恤 | 衣服 | 4000 | 20 |
| 000C | 大阪 | 0004 | 菜刀 | 厨房用具 | 3000 | 50 |
| 000C | 大阪 | 0006 | 叉子 | 厨房用具 | 500 | 90 |
| 000C | 大阪 | 0007 | 擦菜板 | 厨房用具 | 880 | 70 |
| 000D | 福冈 | 0001 | T恤 | 衣服 | 1000 | 100 |
+---------+-----------+------------+--------------+--------------+------------+----------+
13 rows in set (0.01 sec)
我们需要注意的是，连接多张表的时候，最好在选列的时候加上表名的前缀
还有一个问题就是，最好给表起别名，因为原名一般都很长
然后就是这个和关联子查询的一些区别的，应该是比子查询效率高的
因为关联子查询更像是，外查询每遇到一个记录，就去内查询的结果里面找
2.1.2 结合 where
mysql> SELECT *
-> FROM (-- 第一步查询的结果
-> SELECT SP.shop_id
-> ,SP.shop_name
-> ,SP.product_id
-> ,P.product_name
-> ,P.product_type
-> ,P.sale_price
-> ,SP.quantity
-> FROM shopproduct AS SP
-> INNER JOIN product AS P
-> ON SP.product_id = P.product_id) AS STEP1
-> WHERE shop_name = '东京'
-> AND product_type = '衣服' ;
+---------+-----------+------------+--------------+--------------+------------+----------+
| shop_id | shop_name | product_id | product_name | product_type | sale_price | quantity |
+---------+-----------+------------+--------------+--------------+------------+----------+
| 000A | 东京 | 0001 | T恤 | 衣服 | 1000 | 30 |
| 000A | 东京 | 0003 | 运动T恤 | 衣服 | 4000 | 15 |
+---------+-----------+------------+--------------+--------------+------------+----------+
2 rows in set (0.00 sec)
这个感觉也就是先连接表，然后对结果进行了一个筛选
当然我们也可以选择，先对表进行筛选，然后再连接，例如：
mysql> SELECT SP.shop_id
-> ,SP.shop_name
-> ,SP.product_id
-> ,P.product_name
-> ,P.product_type
-> ,P.sale_price
-> ,SP.quantity
->
-> FROM (-- 子查询 1:从 ShopProduct 表筛选出东京商店的信息
-> SELECT *
-> FROM shopproduct
-> WHERE shop_name = '东京' ) AS SP
->
-> INNER JOIN -- 子查询 2:从 Product 表筛选出衣服类商品的信息
->
-> (SELECT *
-> FROM product
-> WHERE product_type = '衣服') AS P
->
-> ON SP.product_id = P.product_id;
+---------+-----------+------------+--------------+--------------+------------+----------+
| shop_id | shop_name | product_id | product_name | product_type | sale_price | quantity |
+---------+-----------+------------+--------------+--------------+------------+----------+
| 000A | 东京 | 0001 | T恤 | 衣服 | 1000 | 30 |
| 000A | 东京 | 0003 | 运动T恤 | 衣服 | 4000 | 15 |
+---------+-----------+------------+--------------+--------------+------------+----------+
2 rows in set (0.00 sec)
在这中间可能会有报错：
Display all 880 possibilities? (y or n)
这是因为混入了 tab，应该全部改为空格
2.1.3 结合 group by
感觉跟 where 差不多，先连接，再筛选
mysql> SELECT SP.shop_id
-> ,SP.shop_name
-> ,MAX(P.sale_price) AS max_price
-> FROM shopproduct AS SP
-> INNER JOIN product AS P
-> ON SP.product_id = P.product_id
-> GROUP BY SP.shop_id,SP.shop_name
-> ;
+---------+-----------+-----------+
| shop_id | shop_name | max_price |
+---------+-----------+-----------+
| 000A | 东京 | 4000 |
| 000B | 名古屋 | 4000 |
| 000C | 大阪 | 4000 |
| 000D | 福冈 | 1000 |
+---------+-----------+-----------+
4 rows in set (0.00 sec)
然后是那个思考题，意思是，现在只有每个商店里面的最高售价
但是不知道这个最高售价是那一个商品
我感觉可能要逐个求每个商店的最高售价及其商品，然后再用并集连接
2.1.4 自然连接
这个也就是，NATURAL JOIN，不用指定 on，自然指定关键字
2.2 外连结
外连接可能类似于求并集，但是又不一样
左连接是以左边的为基准，右边的合并入左边，右连接则相反
2.2.1 左连接
简简单单，根据 product_id 并入左表
mysql> SELECT SP.shop_id
-> ,SP.shop_name
-> ,SP.product_id
-> ,P.product_name
-> ,P.sale_price
-> FROM product AS P
-> LEFT OUTER JOIN shopproduct AS SP
-> ON SP.product_id = P.product_id;
+---------+-----------+------------+--------------+------------+
| shop_id | shop_name | product_id | product_name | sale_price |
+---------+-----------+------------+--------------+------------+
| 000D | 福冈 | 0001 | T恤 | 1000 |
| 000A | 东京 | 0001 | T恤 | 1000 |
| 000B | 名古屋 | 0002 | 打孔器 | 500 |
| 000A | 东京 | 0002 | 打孔器 | 500 |
| 000C | 大阪 | 0003 | 运动T恤 | 4000 |
| 000B | 名古屋 | 0003 | 运动T恤 | 4000 |
| 000A | 东京 | 0003 | 运动T恤 | 4000 |
| 000C | 大阪 | 0004 | 菜刀 | 3000 |
| 000B | 名古屋 | 0004 | 菜刀 | 3000 |
| NULL | NULL | NULL | 高压锅 | 6800 |
| 000C | 大阪 | 0006 | 叉子 | 500 |
| 000B | 名古屋 | 0006 | 叉子 | 500 |
| 000C | 大阪 | 0007 | 擦菜板 | 880 |
| 000B | 名古屋 | 0007 | 擦菜板 | 880 |
| NULL | NULL | NULL | 圆珠笔 | 5000 |
+---------+-----------+------------+--------------+------------+
15 rows in set (0.00 sec)
mysql> select * from product;
+------------+--------------+--------------+------------+----------------+-------------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date |
+------------+--------------+--------------+------------+----------------+-------------+
| 0001 | T恤 | 衣服 | 1000 | 500 | 2009-09-20 |
| 0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 |
| 0003 | 运动T恤 | 衣服 | 4000 | 2800 | NULL |
| 0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20 |
| 0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-01-15 |
| 0006 | 叉子 | 厨房用具 | 500 | NULL | 2009-09-20 |
| 0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28 |
| 0008 | 圆珠笔 | 办公用品 | 5000 | NULL | 2009-11-11 |
+------------+--------------+--------------+------------+----------------+-------------+
8 rows in set (0.00 sec)
mysql> 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)
我们可以看到，左表的内容的得到保留的，例如高压锅和圆珠笔
这两个东西，是只在左边当中的，但是它们的关键字，product_id，没有保留
2.2.2 结合 where
这个没啥好说的，就先连表，再筛选
2.2.3 全外连接
outer join，左右表都保留，但是 mysql 目前不支持
2.3 多表连接
我们已经有表了
mysql> select * from inventoryproduct;
+--------------+------------+--------------------+
| inventory_id | product_id | inventory_quantity |
+--------------+------------+--------------------+
| P001 | 0001 | 0 |
| P001 | 0002 | 120 |
| P001 | 0003 | 200 |
| P001 | 0004 | 3 |
| P001 | 0005 | 0 |
| P001 | 0006 | 99 |
| P001 | 0007 | 999 |
| P001 | 0008 | 200 |
| P002 | 0001 | 10 |
| P002 | 0002 | 25 |
| P002 | 0003 | 34 |
| P002 | 0004 | 19 |
| P002 | 0005 | 99 |
| P002 | 0006 | 0 |
| P002 | 0007 | 0 |
| P002 | 0008 | 18 |
+--------------+------------+--------------------+
16 rows in set (0.00 sec)
2.3.1 多表内连接
查一下各个商店的各件商品的库存
mysql> SELECT SP.shop_id
-> ,SP.shop_name
-> ,SP.product_id
-> ,P.product_name
-> ,P.sale_price
-> ,IP.inventory_quantity
-> FROM shopproduct AS SP
-> INNER JOIN product AS P
-> ON SP.product_id = P.product_id
-> INNER JOIN inventoryproduct AS IP
-> ON SP.product_id = IP.product_id
-> WHERE IP.inventory_id = 'P001';
+---------+-----------+------------+--------------+------------+--------------------+
| shop_id | shop_name | product_id | product_name | sale_price | inventory_quantity |
+---------+-----------+------------+--------------+------------+--------------------+
| 000A | 东京 | 0001 | T恤 | 1000 | 0 |
| 000A | 东京 | 0002 | 打孔器 | 500 | 120 |
| 000A | 东京 | 0003 | 运动T恤 | 4000 | 200 |
| 000B | 名古屋 | 0002 | 打孔器 | 500 | 120 |
| 000B | 名古屋 | 0003 | 运动T恤 | 4000 | 200 |
| 000B | 名古屋 | 0004 | 菜刀 | 3000 | 3 |
| 000B | 名古屋 | 0006 | 叉子 | 500 | 99 |
| 000B | 名古屋 | 0007 | 擦菜板 | 880 | 999 |
| 000C | 大阪 | 0003 | 运动T恤 | 4000 | 200 |
| 000C | 大阪 | 0004 | 菜刀 | 3000 | 3 |
| 000C | 大阪 | 0006 | 叉子 | 500 | 99 |
| 000C | 大阪 | 0007 | 擦菜板 | 880 | 999 |
| 000D | 福冈 | 0001 | T恤 | 1000 | 0 |
+---------+-----------+------------+--------------+------------+--------------------+
13 rows in set (0.00 sec)
同样是先连表，再筛选
2.3.1 多表外连接
换一换关键词，其他没啥区别
2.4 非等值连接
这大概意思是，要选出来比自己售价高的商品，然后计数，作为该商品的排名
mysql> SELECT product_id
-> ,product_name
-> ,sale_price
-> ,COUNT(p2_id) AS my_rank
-> -- ,P2_id
-> 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
-> ) AS X
-> GROUP BY product_id, product_name, sale_price
-> ORDER BY my_rank
-> ;
+------------+--------------+------------+---------+
| product_id | product_name | sale_price | my_rank |
+------------+--------------+------------+---------+
| 0005 | 高压锅 | 6800 | 1 |
| 0008 | 圆珠笔 | 5000 | 2 |
| 0003 | 运动T恤 | 4000 | 3 |
| 0004 | 菜刀 | 3000 | 4 |
| 0001 | T恤 | 1000 | 5 |
| 0007 | 擦菜板 | 880 | 6 |
| 0002 | 打孔器 | 500 | 8 |
| 0006 | 叉子 | 500 | 8 |
+------------+--------------+------------+---------+
8 rows in set (0.00 sec)
反正连接条件依赖于 on 指定的判断，调整为大于即可
然后例如说圆珠笔，它有两个比它售价高的商品
那么在中间的某个表上，它就与这两个商品的记录做连接
例如：
mysql> SELECT product_id
-> ,product_name
-> ,sale_price
-> -- ,COUNT(p2_id) AS my_rank
-> ,P2_id
-> 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
-> ) AS X
-> -- GROUP BY product_id, product_name, sale_price
-> -- ORDER BY my_rank
-> ;
+------------+--------------+------------+-------+
| product_id | product_name | sale_price | P2_id |
+------------+--------------+------------+-------+
| 0001 | T恤 | 1000 | 0008 |
| 0001 | T恤 | 1000 | 0005 |
| 0001 | T恤 | 1000 | 0004 |
| 0001 | T恤 | 1000 | 0003 |
| 0001 | T恤 | 1000 | 0001 |
| 0002 | 打孔器 | 500 | 0008 |
| 0002 | 打孔器 | 500 | 0007 |
| 0002 | 打孔器 | 500 | 0006 |
| 0002 | 打孔器 | 500 | 0005 |
| 0002 | 打孔器 | 500 | 0004 |
| 0002 | 打孔器 | 500 | 0003 |
| 0002 | 打孔器 | 500 | 0002 |
| 0002 | 打孔器 | 500 | 0001 |
| 0003 | 运动T恤 | 4000 | 0008 |
| 0003 | 运动T恤 | 4000 | 0005 |
| 0003 | 运动T恤 | 4000 | 0003 |
| 0004 | 菜刀 | 3000 | 0008 |
| 0004 | 菜刀 | 3000 | 0005 |
| 0004 | 菜刀 | 3000 | 0004 |
| 0004 | 菜刀 | 3000 | 0003 |
| 0005 | 高压锅 | 6800 | 0005 |
| 0006 | 叉子 | 500 | 0008 |
| 0006 | 叉子 | 500 | 0007 |
| 0006 | 叉子 | 500 | 0006 |
| 0006 | 叉子 | 500 | 0005 |
| 0006 | 叉子 | 500 | 0004 |
| 0006 | 叉子 | 500 | 0003 |
| 0006 | 叉子 | 500 | 0002 |
| 0006 | 叉子 | 500 | 0001 |
| 0007 | 擦菜板 | 880 | 0008 |
| 0007 | 擦菜板 | 880 | 0007 |
| 0007 | 擦菜板 | 880 | 0005 |
| 0007 | 擦菜板 | 880 | 0004 |
| 0007 | 擦菜板 | 880 | 0003 |
| 0007 | 擦菜板 | 880 | 0001 |
| 0008 | 圆珠笔 | 5000 | 0008 |
| 0008 | 圆珠笔 | 5000 | 0005 |
+------------+--------------+------------+-------+
37 rows in set (0.00 sec)
我们可以看到比圆珠笔高的是 8 号和 5 号商品
其分别与这两条记录做了连接
2.5 交叉连接
cross join，听起来挺像outer join，但是不一样
这个更多，匹配不匹配都做一遍连接
mysql> SELECT SP.shop_id
-> ,SP.shop_name
-> ,SP.product_id
-> ,P.product_name
-> ,P.sale_price
-> FROM shopproduct AS SP
-> CROSS JOIN product AS P;
+---------+-----------+------------+--------------+------------+
| shop_id | shop_name | product_id | product_name | sale_price |
+---------+-----------+------------+--------------+------------+
| 000A | 东京 | 0001 | 圆珠笔 | 5000 |
......
+---------+-----------+------------+--------------+------------+
104 rows in set (0.00 sec)
也可以选择隐式地连接
SELECT SP.shop_id
,SP.shop_name
,SP.product_id
,P.product_name
,P.sale_price
FROM ShopProduct AS SP , Product AS P;
交叉连接又叫笛卡尔积，就是那个一一配对的
内连接和外连接，其实也就是对交叉连接的限制和过滤
2.6 语法问题
我们在上面提到过，交叉连接有隐式地方式
如果采用了这种方式，那代码可能会变得不便阅读
而且配合上 where 之后，更难判断结果是内连接还是外连接
A.练习题
A.1
找出 product 和 product2 中售价高于 500 的商品的基本信息。
mysql> SELECT *
-> FROM product
-> WHERE sale_price>500
->
-> UNION
->
-> SELECT *
-> FROM product2
-> WHERE sale_price>500
-> ;
+------------+--------------+--------------+------------+----------------+-------------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date |
+------------+--------------+--------------+------------+----------------+-------------+
| 0001 | T恤 | 衣服 | 1000 | 500 | 2009-09-20 |
| 0003 | 运动T恤 | 衣服 | 4000 | 2800 | NULL |
| 0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20 |
| 0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-01-15 |
| 0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28 |
| 0008 | 圆珠笔 | 办公用品 | 5000 | NULL | 2009-11-11 |
| 0009 | 手套 | 衣服 | 800 | 500 | NULL |
| 0010 | 水壶 | 厨房用具 | 2000 | 1700 | 2009-09-20 |
+------------+--------------+--------------+------------+----------------+-------------+
8 rows in set (0.01 sec)
A.2
借助对称差的实现方式, 求product和product2的交集。
mysql> SELECT *
-> FROM product
-> WHERE product_id IN (SELECT product_id FROM product2)
->
-> UNION
->
-> SELECT *
-> FROM product2
-> WHERE product_id IN (SELECT product_id FROM product)
-> ;
+------------+--------------+--------------+------------+----------------+-------------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date |
+------------+--------------+--------------+------------+----------------+-------------+
| 0001 | T恤 | 衣服 | 1000 | 500 | 2009-09-20 |
| 0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 |
| 0003 | 运动T恤 | 衣服 | 4000 | 2800 | NULL |
+------------+--------------+--------------+------------+----------------+-------------+
3 rows in set (0.00 sec)
A.3
每类商品中售价最高的商品都在哪些商店有售 ？
mysql> SELECT p1.product_id, p1.product_name, p1.product_type, p1.sale_price, p2.max_sale_price, sp.shop_name
-> -- SELECT *
-> 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
-> -- ;
-> INNER JOIN
-> shopproduct AS sp
-> ON sp.product_id = p1.product_id
-> -- ;
-> WHERE p1.sale_price = p2.max_sale_price
-> ;
+------------+--------------+--------------+------------+----------------+-----------+
| product_id | product_name | product_type | sale_price | max_sale_price | shop_name |
+------------+--------------+--------------+------------+----------------+-----------+
| 0003 | 运动T恤 | 衣服 | 4000 | 4000 | 东京 |
| 0003 | 运动T恤 | 衣服 | 4000 | 4000 | 名古屋 |
| 0003 | 运动T恤 | 衣服 | 4000 | 4000 | 大阪 |
+------------+--------------+--------------+------------+----------------+-----------+
3 rows in set (0.01 sec)
这看起来可能有点奇怪，我们看一看其他情况：
mysql> -- SELECT p1.product_id, p1.product_name, p1.product_type, p1.sale_price, p2.max_sale_price, sp.shop_name
mysql> SELECT *
-> 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
-> -- ;
-> -- INNER JOIN
-> -- shopproduct AS sp
-> -- ON sp.product_id = p1.product_id
-> -- ;
-> WHERE p1.sale_price = p2.max_sale_price
-> ;
+------------+--------------+--------------+------------+----------------+-------------+--------------+----------------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date | product_type | max_sale_price |
+------------+--------------+--------------+------------+----------------+-------------+--------------+----------------+
| 0003 | 运动T恤 | 衣服 | 4000 | 2800 | NULL | 衣服 | 4000 |
| 0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-01-15 | 厨房用具 | 6800 |
| 0008 | 圆珠笔 | 办公用品 | 5000 | NULL | 2009-11-11 | 办公用品 | 5000 |
+------------+--------------+--------------+------------+----------------+-------------+--------------+----------------+
3 rows in set (0.00 sec)
mysql> 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)
噢，另外两类售价最高的商品，没有商店卖，分别是 5 和 8
A.4
分别使用内连结和关联子查询每一类商品中售价最高的商品。
嗯，内连接的情况，好像上面已经做过了，先建一个子表，然后连起来
mysql> SELECT *
-> 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
-> WHERE p1.sale_price = p2.max_sale_price
-> ;
+------------+--------------+--------------+------------+----------------+-------------+--------------+----------------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date | product_type | max_sale_price |
+------------+--------------+--------------+------------+----------------+-------------+--------------+----------------+
| 0003 | 运动T恤 | 衣服 | 4000 | 2800 | NULL | 衣服 | 4000 |
| 0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-01-15 | 厨房用具 | 6800 |
| 0008 | 圆珠笔 | 办公用品 | 5000 | NULL | 2009-11-11 | 办公用品 | 5000 |
+------------+--------------+--------------+------------+----------------+-------------+--------------+----------------+
3 rows in set (0.00 sec)
关联子查询如下：
mysql> SELECT *
-> FROM product AS p1
-> WHERE sale_price = (
-> SELECT MAX(sale_price)
-> FROM product AS p2
-> WHERE p1.product_type = p2.product_type
-> GROUP BY product_type)
-> ;
+------------+--------------+--------------+------------+----------------+-------------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date |
+------------+--------------+--------------+------------+----------------+-------------+
| 0003 | 运动T恤 | 衣服 | 4000 | 2800 | NULL |
| 0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-01-15 |
| 0008 | 圆珠笔 | 办公用品 | 5000 | NULL | 2009-11-11 |
+------------+--------------+--------------+------------+----------------+-------------+
3 rows in set (0.00 sec)
A.5
用关联子查询实现：在 product 表中，取出 product_id, product_name, sale_price, 并按照商品的售价从低到高进行排序、对售价进行累计求和。
mysql> SELECT p1.product_id, p1.product_name, p1.sale_price,
-> (
-> SELECT SUM(DISTINCT(CASE WHEN p2.sale_price < p1.sale_price THEN p2.sale_price ELSE 0 END)) + p1.sale_price
-> FROM product AS p2
-> -- WHERE p2.sale_price < p1.sale_price
-> ) AS cnt_price
-> FROM product AS p1
-> ORDER BY p1.sale_price
-> ;
+------------+--------------+------------+-----------+
| product_id | product_name | sale_price | cnt_price |
+------------+--------------+------------+-----------+
| 0002 | 打孔器 | 500 | 500 |
| 0006 | 叉子 | 500 | 500 |
| 0007 | 擦菜板 | 880 | 1380 |
| 0001 | T恤 | 1000 | 2380 |
| 0004 | 菜刀 | 3000 | 5380 |
| 0003 | 运动T恤 | 4000 | 9380 |
| 0008 | 圆珠笔 | 5000 | 14380 |
| 0005 | 高压锅 | 6800 | 21180 |
+------------+--------------+------------+-----------+
8 rows in set (0.00 sec)
不太清楚这个累计是啥意思
我这里的方式是，累计小于等于该商品的价位，每个价位只计算一次
啊，我可能大概了解了这个意思，在学习窗口函数时
以上结果应该在第二行是不对的，它的累计值应该是 1000
我感觉在排序之后，应该新增一列序号列
然后再让每一行累计小于等于其序号的售价
844
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
