导读
作者:松华老师源码版本:MySQL 8.0.13
需求描述
这两天,开发人员提了一个比较特别的SQL需求,需求背景是: 字典表里没有记录,而实际的那张表里面有相应的记录。虽然可以直接在字典表中为每个组,加一行数据,但是我们无法判断加了这一行之后的影响范围。因此使用影响范围最小的方式,通过 SQL 查询出完整数据。
具体内容如下 :
现在两个表W1和W2,表结构及数据情况如下:
select * from w1;+------+------+------+| a | b | b1 |+------+------+------+| a | b | 0 || b | b | b1 || c | b | b2 || d | b | b3 || e | c | 0 || c | c | c1 |+------+------+------+6 rows in set (0.00 sec) select * from w2;+------+------+| b | b1 |+------+------+| b | b1 || b | b2 || b | b3 || b | b4 || c | c1 || c | c2 || c | c3 |+------+------+7 rows in set (0.00 sec)
想要得到的数据结果集如下:
+------+---+----+| a | b | b1 |+------+---+----+| a | b | 0 || b | b | b1 || c | b | b2 || d | b | b3 || a | b | b4 || e | c | 0 || c | c | c1 || e | c | c2 || e | c | c3 |+------+---+----+
步骤分析
表w1和w2 中,字段b的值为‘b’的记录分别有:
select *from w1 where b='b'; +------+------+------+| a | b | b1 |+------+------+------+| a | b | 0 || b | b | b1 || c | b | b2 || d | b | b3 |+------+------+------+4 rows in set (0.00 sec) select *from w2 where b='b';+------+------+| b | b1 |+------+------+| b | b1 || b | b2 || b | b3 || b | b4 |+------+------+4 rows in set (0.00 sec)
对以上部分记录的补全新需求,整理为:
表w1做主表, 补全表w2的数据部分, 补全部分数据为表w1的字段b1=0对应记录的字段a的值。
补全结果集为:
+------+---+----+| a | b | b1 |+------+---+----+| a | b | 0 || b | b | b1 || c | b | b2 || d | b | b3 || a | b | b4 |+------+---+----+
所补充的记录为:
+------+---+----+| a | b | b1 |+------+---+----+| a | b | b4 |+------+---+----+
从最终结果分析,首先需要基表w2 left join w1,关联条件为:w2.b=w1.b and w2.b1=w1.b1 ,结果如下;
->select w1.a ,w2.b,w2.b1 from w2 left join w1 on w2.b= w1.b and w2.b1=w1.b1;+------+------+------+| a | b | b1 |+------+------+------+| b | b | b1 || c | b | b2 || d | b | b3 || c | c | c1 || NULL | b | b4 || NULL | c | c2 || NULL | c | c3 |+------+------+------+7 rows in set (0.00 sec)
但是,从结果上看少了两行,即 b1=0的部分。
此时,使用 full outer join 就可以 解决这个问题。
由于MySQL不支持full outer join,所以使用替代方案left outer join union right outer join 。
select w2.b w2b , w2.b1 w2b1 ,w1.* from w2 left join w1 on w2.b= w1.b and w2.b1=w1.b1 union select * from w2 right join w1 on w2.b= w1.b and w2.b1=w1.b1;# 结果集如下:+------+------+------+------+------+| w2b | w2b1 | a | b | b1 |+------+------+------+------+------+| b | b1 | b | b | b1 || b | b2 | c | b | b2 || b | b3 | d | b | b3 || c | c1 | c | c | c1 || b | b4 | NULL | NULL | NULL || c | c2 | NULL | NULL | NULL || c | c3 | NULL | NULL | NULL || NULL | NULL | a | b | 0 || NULL | NULL | e | c | 0 |+------+------+------+------+------+9 rows in set (0.00 sec)
稍微美化一下SQL,结果如下:
select w3.a ,case when w3.b is null then w3.w2b else w3.b end b ,case when w3.b1 is null then w3.w2b1 else w3.b1 end b1 from ( select w2.b w2b , w2.b1 w2b1 ,w1.* from w2 left join w1 on w2.b= w1.b and w2.b1=w1.b1 union select * from w2 right join w1 on w2.b= w1.b and w2.b1=w1.b1 )w3 order by b, b1;# 结果集如下:+------+------+------+| a | b | b1 |+------+------+------+| a | b | 0 || b | b | b1 || c | b | b2 || d | b | b3 || NULL | b | b4 || e | c | 0 || c | c | c1 || NULL | c | c2 || NULL | c | c3 |+------+------+------+9 rows in set (0.00 sec)
现在我们离最终想要的结果集只差一步了,关于a 的空部分 ,有两种办法可以实现:
用标量子查询查一次表w1表求出对应的字段a
用窗口函数
实现方式
在本案例中,我们用窗口函数方法 max() over (partition by ):
select w4.*,max(case when w4.b1='0' then w4.a end) over(partition by w4.b) m1 from (select w3.a ,case when w3.b is null then w3.w2b else w3.b end b ,case when w3.b1 is null then w3.w2b1 else w3.b1 end b1 from ( select w2.b w2b , w2.b1 w2b1 ,w1.* from w2 left join w1 on w2.b= w1.b and w2.b1=w1.b1 union select * from w2 right join w1 on w2.b= w1.b and w2.b1=w1.b1 ) w3 order by b , b1 ) w4;# 结果集如下:+------+------+------+------+| a | b | b1 | m1 |+------+------+------+------+| a | b | 0 | a || b | b | b1 | a || c | b | b2 | a || d | b | b3 | a || NULL | b | b4 | a || e | c | 0 | e || c | c | c1 | e || NULL | c | c2 | e || NULL | c | c3 | e |+------+------+------+------+9 rows in set (0.00 sec)
如上,已经得出了一个列m1。为了满足需求,我们下一步使用case when处理,就可以得出最终结果了,详情如下:
select case when a is null then m1 else a end a ,b ,b1 from ( select w4.* ,max(case when w4.b1='0' then w4.a end ) over(partition by w4.b) m1 from (select w3.a ,case when w3.b is null then w3.w2b else w3.b end b ,case when w3.b1 is null then w3.w2b1 else w3.b1 end b1 from ( select w2.b w2b , w2.b1 w2b1 ,w1.* from w2 left join w1 on w2.b= w1.b and w2.b1=w1.b1 union select * from w2 right join w1 on w2.b= w1.b and w2.b1=w1.b1 ) w3 order by b , b1 ) w4 ) w5 ;# 结果集如下;+------+------+------+| a | b | b1 |+------+------+------+| a | b | 0 || b | b | b1 || c | b | b2 || d | b | b3 || a | b | b4 || e | c | 0 || c | c | c1 || e | c | c2 || e | c | c3 |+------+------+------+9 rows in set (0.00 sec)
至此,本次需求圆满满足。
本案例中有两种实现方案,本文中仅使用了“窗口函数”方式实现需求。童鞋们可以自行尝试使用另外一种方法“标量子查询”完成 SQL。
欢迎在评论区或者相关群里贴出各位的成果~~~
暖心贴士
松华老师历史好文回顾:
不用改表,老司机只用了这招提升千倍性能
SQL优化案例精彩连载(一)
实战演练(二):运行20小时的报表SQL优化后秒出
松华老师历史公开课回顾:
知数堂郑松华:MySQL 8.0 SQL优化之CTE 、窗口函数的应用
公开课发布《从50分到0.59秒,SQL优化让不可能变成可能》by松华
加入知数堂
挑战40万+年薪!
知数堂
叶金荣与吴炳锡联合打造
领跑IT精英培训
行业资深专家强强联合,倾心定制
MySQL的实战/ MySQL的优化/ MongoDB的/
Python / SQL优化/ Hadoop + ELK
数门精品课程
“阅读原文”可获更多正课试听视频
密码:hg3h
紧随技术发展趋势,定期优化培训教案
融入大量生产案例,贴合企业一线需求
社群陪伴学习,一次报名,可学1年
DBA,开发工程师必修课
上千位学员已华丽转身,薪资翻番,职位提升
改变已悄然发生,你还在等什么?
扫码加入QQ技术交流群
MySQL的入门到精通-ZST
(QQ群号:793818397)