Python社区  »  DATABASE

关于MySQL复杂需求实现

老叶茶馆 • 4 月前 • 64 次点击  


导读

作者:松华老师
源码版本: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 的空部分 ,有两种办法可以实现:

  1. 用标量子查询查一次表w1表求出对应的字段a

  2. 用窗口函数




实现方式



在本案例中,我们用窗口函数方法  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)    



Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/32415
 
64 次点击  
分享到微博