关于MySQL复杂需求实现

导读

作者：松华老师
源码版本：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)