MySQL Join竟然可以这么玩？根据条件进行复制

导读：

本文节选自松华老师的《SQL优化专栏》

郑松华，知数堂SQL 优化班老师 

现任 CCmediaService DBA，主要负责数据库优化相关工作

擅长SQL优化 ，数据核对

想阅读更多内容请点击订阅专栏

大家好，我是知数堂SQL优化班老师，网名：骑龟的兔子

今天给大家带来根据条件进行复制的案例

背景说明：

如下，有一个表t1 ,有三行数据

with t1 as (
select 1 seq ,'AK' id ,'A' type ,'g1' goods from dual union all
select 2 seq ,'PS' id ,'B' type ,'iphone' goods from dual union all
select 3 seq ,'BE' id ,'B' type ,'g2' goods from dual
)
select * from t1 ;
+-----+----+------+--------+
| seq | id | type | goods |
+-----+----+------+--------+
| 1 | AK | A | g1 |
| 2 | PS | B | iphone |
| 3 | BE | B | g2 |
+-----+----+------+--------+
3 rows in set (0.00 sec)

现在需求是，根据type 的值'A','B' 要求，如果是'A' 那么还是原来的行数

如果是'B' 那么需要复制五行数据。

如下所示

no | seq | id | type | goods
1, 1, AK, A, g1
2, 2, PS, B, iphone
3, 2, PS, B, t2
4, 2, PS, B, t3



    
5, 2, PS, B, t4
6, 2, PS, B, t5
7, 3, BE, B, g2
8, 3, BE, B, t2
9, 3, BE, B, t3
10, 3, BE, B, t4
11, 3, BE, B, t5

思路：

我们先来对这个需求进行分析，

第一、因为对行进行复制，所以需要一个复制表

第二、根据条件进行复制

第三、对每个条件的第一个值用t1表的goods进行替换

优化过程：

根据如上所示，

第一、因为对行进行复制，所以需要一个复制表：

先创建一个复制表，这里所涉及两种情况，因为最多复制5行，A种类型可以看做是id为1

with t2 as (
select 1 id ,'t1' a1 from dual union all
select 2 id ,'t2' a1 from dual union all
select 3 id ,'t3' a1 from dual union all
select 4 id ,'t4' a1 from dual union all
select 5 id ,'t5' a1 from dual
)
select * from t2
+----+----+
| id | a1 |
+----+----+
| 1 | t1 |
| 2 | t2 |
| 3 | t3 |
| 4 | t4 |
| 5 | t5 |
+----+----+

第二、根据条件进行复制：

我们这里设置了一个连接条件 ，就是当type='A'的时候我们返回1

当type='B'的时候 我们返回5

然后我们进行了一个>= join

with t1 as (
select 1 seq ,'AK' id ,'A' type ,'g1' goods from dual union all
select 2 seq ,'PS' id ,'B' type ,'iphone' goods from dual union all
select 3 seq ,'BE' id ,'B' type ,'g2' goods from dual
),
 t2 as (
select 1 id ,'t1' a1 from dual union all
select 2 id ,'t2' a1 from dual union all
select 3 id ,'t3' a1 from dual union all



    
select 4 id ,'t4' a1 from dual union all
select 5 id ,'t5' a1 from dual
)
select * from t1 join t2
 on case when t1.type = 'A' then 1
 when t1.type = 'B' then 5 end >= t2.id
 order by seq ,t1.id
+-----+----+------+--------+----+----+
| seq | id | type | goods | id | a1 |
+-----+----+------+--------+----+----+
| 1 | AK | A | g1 | 1 | t1 |
| 2 | PS | B | iphone | 1 | t1 |
| 2 | PS | B | iphone | 2 | t2 |
| 2 | PS | B | iphone | 3 | t3 |
| 2 | PS | B | iphone | 4 | t4 |
| 2 | PS | B | iphone | 5 | t5 |
| 3 | BE | B | g2 | 1 | t1 |
| 3 | BE | B | g2 | 2 | t2 |
| 3 | BE | B | g2 | 3 | t3 |
| 3 | BE | B | g2 | 4 | t4 |
| 3 | BE | B | g2 | 5 | t5 |
+-----+----+------+--------+----+----+

如上所示，我们就完成了 有条件的复制

剩下就剩最后一步

第三、对每个条件的第一个值用t1表的goods进行替换：

我们可以利用case when 语句对t2.id =1的时候用t1.goods其余的用t2.a1进行替换

with t1 as (
select 1 seq ,'AK' id ,'A' type ,'g1' goods from dual union all
select 2 seq ,'PS' id ,'B' type ,'iphone' goods from dual union all
select 3 seq ,'BE' id ,'B' type ,'g2' goods from dual
),
 t2 as (
select 1 id ,'t1' a1 from dual union all
select 2 id ,'t2' a1 from dual union all
select 3 id ,'t3' a1 from dual union all
select 4 id ,'t4' a1 from dual union all
select 5 id ,'t5' a1 from dual
)
select
t1.seq,t1.id,t1.type,
case when t2.id =1 then t1.goods
else t2.a1 end goods 
 from t1 join t2
 on case when t1.type = 'A' then 1
 when t1.type = 'B' then 5 end >= t2.id
 order by seq ,t1.id
 +-----+----+------+--------+
| seq | id | type | goods |
+-----+----+------+--------+
| 1 | AK | A | g1 |
| 2 | PS | B | iphone |
| 2 | PS | B | t2 |
| 2 | PS | B | t3 |
| 2 | PS | B | t4 |
| 2 | PS | B | t5 |
| 3 | BE | B | g2 |
| 3 | BE | B | t2 |
| 3 | BE | B | t3 |
| 3 | BE | B | t4 |
| 3 | BE | B | t5 |
+-----+----+------+--------+

最后一步就是赋予序列号，MySQL 8.0 有窗口函数

with t1 as (
select 1 seq ,'AK' id ,'A' type ,'g1' goods from dual union all
select 2 seq ,'PS' id ,'B' type ,'iphone' goods from dual union all
select 3 seq ,'BE' id ,'B' type ,'g2' goods from dual
),
 t2 as (
select 1 id ,'t1' a1 from dual union all
select 2 id ,'t2' a1 from dual union all
select 3 id ,'t3' a1 from dual union all
select 4 id ,'t4' a1 from dual union all



    
select 5 id ,'t5' a1 from dual
)
select
row_number() over( order by t1.seq ,t1.id) no ,
t1.seq,t1.id,t1.type,
case when t2.id =1 then t1.goods
else t2.a1 end goods
 from t1 join t2
 on case when t1.type = 'A' then 1
 when t1.type = 'B' then 5 end >= t2.id
+----+-----+----+------+--------+
| no | seq | id | type | goods |
+----+-----+----+------+--------+
| 1 | 1 | AK | A | g1 |
| 2 | 2 | PS | B | iphone |
| 3 | 2 | PS | B | t2 |
| 4 | 2 | PS | B | t3 |
| 5 | 2 | PS | B | t4 |
| 6 | 2 | PS | B | t5 |
| 7 | 3 | BE | B | g2 |
| 8 | 3 | BE | B | t2 |
| 9 | 3 | BE | B | t3 |
| 10 | 3 | BE | B | t4 |
| 11 | 3 | BE | B | t5 |
+----+-----+----+------+--------+

谢谢大家~ 欢迎转发

如有关于SQL优化方面疑问需要交流的，请加入QQ群（579036588），并@骑兔子的龟 就可与我联系

END