外连接的优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
-- 商品表
+--------+------+
| number | item |
+--------+------+
|     10 | FD   |
|     20 | CD-R |
|     30 | MO   |
|     40 | DVD  |
+--------+------+
-- 销售表
+--------+----------+
| number | quantity |
+--------+----------+
|     10 |        4 |
|     20 |       10 |
|     30 |        3 |
|     10 |       32 |
|     30 |       12 |
|     20 |       22 |
|     30 |        7 |
+--------+----------+
-- 预期结果
+--------+--------------+
| number | sum_quantity |
+--------+--------------+
|     10 |           36 |
|     20 |           32 |
|     30 |           22 |
|     40 |         NULL |
+--------+--------------+
-- 若单纯使用销售表统计无法得到最后一行
select number, sum(quantity) from salehistory group by number;
+--------+---------------+
| number | sum(quantity) |
+--------+---------------+
|     10 |            36 |
|     20 |            32 |
|     30 |            22 |
+--------+---------------+
-- 若想得到最后一行需要外连接
select g.number, h.sum_quantity from goods as g left join (select number, sum(quantity) as sum_quantity from salehistory group by number) as h on g.number=h.number;
-- 上述查询会使用到临时表，可以通过先建立连接再分组的方式优化
select g.number, sum(s.quantity) from goods as g left join salehistory as s on g.number=s.number group by g.number;

TODO：求异或集

利用自连接进行行间比较

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
-- 原数据
+------+------+
| year | sale |
+------+------+
| 1990 |   50 |
| 1991 |   51 |
| 1992 |   52 |
| 1993 |   52 |
| 1994 |   50 |
| 1995 |   50 |
| 1996 |   49 |
| 1997 |   55 |
+------+------+
-- 要求查出销售增减情况
select s1.year,s1.sale,s1.sale-s2.sale from sales as s1, sales as s2 where s2.year=s1.year-1;
+------+------+-----------------+
| year | sale | s1.sale-s2.sale |
+------+------+-----------------+
| 1991 |   51 |               1 |
| 1992 |   52 |               1 |
| 1993 |   52 |               0 |
| 1994 |   50 |              -2 |
| 1995 |   50 |               0 |
| 1996 |   49 |              -1 |
| 1997 |   55 |               6 |
+------+------+-----------------+
-- TODO：添加1990年的数据
-- 时间有断层的情况
+------+------+
| year | sale |
+------+------+
| 1990 |   50 |
| 1992 |   52 |
| 1993 |   52 |
| 1994 |   50 |
| 1997 |   55 |
+------+------+
select s1.year,s1.sale,s1.sale-s2.sale from sales as s1, sales as s2 where s2.year=(select max(s3.year) from sales s3 where s1.year>s3.year);
-- 其中select max(s3.year) from sales s3 where s1.year>s3.year表示过去的年份中最近的一个即上一行
+------+------+-----------------+
| year | sale | s1.sale-s2.sale |
+------+------+-----------------+
| 1992 |   52 |               2 |
| 1993 |   52 |               0 |
| 1994 |   50 |              -2 |
| 1997 |   55 |               5 |
+------+------+-----------------+
-- 若想出现1990年的数据则把上述sql改成外连接
select s1.year,s1.sale,s1.sale-s2.sale from sales as s1 left join sales as s2 on s2.year=(select max(s3.year) from sales s3 where s1.year>s3.year) order by s1.year;
+------+------+-----------------+
| year | sale | s1.sale-s2.sale |
+------+------+-----------------+
| 1990 |   50 |            NULL |
| 1992 |   52 |               2 |
| 1993 |   52 |               0 |
| 1994 |   50 |              -2 |
| 1997 |   55 |               5 |
+------+------+-----------------+

求累计值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- mysql8使用窗口函数
select sum(sale) over(order by year) as sum_sale from sales;
-- 不使用窗口函数(冯诺依曼型递归集合)
select (select sum(sale) from sales as s2 where s1.year>=s2.year) as sum_sale from sales as s1;
-- 结果
+-----------+
| sum_money |
+-----------+
|        50 |
|       102 |
|       154 |
|       204 |
|       259 |
+-----------+

窗口函数求累计值可以指定步长

1
2
select sum(sale) over(order by year rows 2 preceding) as sum_sale from sales;
TODO: 若不使用窗口函数

查询重叠的时间

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 下表为酒店某个房间的预定情况
+----------+------------+------------+
| reserver | start_time | end_time   |
+----------+------------+------------+
| 张三     | 2020-01-01 | 2020-01-02 |
| 李四     | 2020-01-03 | 2020-01-05 |
| 王五     | 2020-01-04 | 2020-01-06 |
+----------+------------+------------+
-- 王五和李四预定时间重叠
select * from reservation as r1 where exists (
select * from reservation as r2 where r1.reserver<>r2.reserver 
and 
(r1.start_time between r2.start_time and r2.end_time or r1.end_time between r2.start_time and r2.end_time) 
);
-- 自己的入店时间在别人入店和离店时间之间 或 自己的离店时间在别人的入店和离店时间之间

集合运算

1
2
3
4
5
6
-- 比价两张表是否相等
select count(*) from (
	select * from user union 
	select * from user4
) as u;
-- union去除重复行，若两表完全相等则union之后仍和原表相等，相应行数不变，a union a = a成为幂等性

删除连续的重复行

1
delete from user where id not in (select max(id) from user group by name,age,...);