Mysql巧用join优化sql的方法详解(2)-木庄网络博客

例子：将取经组中每个电话号码变成一行

原始数据：

+-----------+---------------------------------+
| user_name | mobile             |
+-----------+---------------------------------+
| 唐僧   | 138245623,021-382349      |
| 孙悟空  | 159384292,022-483432,+86-392432 |
| 猪八戒  | 183208243,055-8234234      |
| 沙僧   | 293842295,098-2383429      |
| NULL   | 993267899            |
+-----------+---------------------------------+

想要得到的数据：

+-----------+-------------+
| user_name | mobile   |
+-----------+-------------+
| 唐僧   | 138245623  |
| 唐僧   | 021-382349 |
| 孙悟空  | 159384292  |
| 孙悟空  | 022-483432 |
| 孙悟空  | +86-392432 |
| 猪八戒  | 183208243  |
| 猪八戒  | 055-8234234 |
| 沙僧   | 293842295  |
| 沙僧   | 098-2383429 |
| NULL   | 993267899  |
+-----------+-------------+

可以看到唐僧有两个电话，因此他就需要两行。我们可以先求出每人的电话号码数量，然后与一张序列表进行笛卡儿积关联，为了节约篇幅，只取出唐僧的数据

select a.id, b.* from tb_sequence a cross join (select user_name, mobile, length(mobile)-length(replace(mobile, ',', ''))+1 size from user1) b order by 2,1;

+----+-----------+---------------------------------+------+
| id | user_name | mobile             | size |
+----+-----------+---------------------------------+------+
| 1 | 唐僧   | 138245623,021-382349      |  2 |
| 2 | 唐僧   | 138245623,021-382349      |  2 |
| 3 | 唐僧   | 138245623,021-382349      |  2 |
| 4 | 唐僧   | 138245623,021-382349      |  2 |
| 5 | 唐僧   | 138245623,021-382349      |  2 |
| 6 | 唐僧   | 138245623,021-382349      |  2 |
| 7 | 唐僧   | 138245623,021-382349      |  2 |
| 8 | 唐僧   | 138245623,021-382349      |  2 |
| 9 | 唐僧   | 138245623,021-382349      |  2 |
| 10 | 唐僧   | 138245623,021-382349      |  2 |
+----+-----------+---------------------------------+------+

a.id对应的就是第几个电话号码，size就是总的电话号码数量，因此可以加上关联条件(a.id <= b.size)，将上面的sql继续调整

select b.user_name, replace(substring(substring_index(b.mobile, ',', a.id), char_length(substring_index(mobile, ',', a.id-1)) + 1), ',', '') as mobile from tb_sequence a cross join (select user_name, concat(mobile, ',') as mobile, length(mobile)-length(replace(mobile, ',', ''))+1 size from user1) b on (a.id <= b.size);

6. 使用笛卡尔积关联实现多列转多行

例子：将取经组中每件装备变成一行

原始数据：

+----+-----------+--------------+-----------------+-----------------+
| id | user_name | arms     | clothing    | shoe      |
+----+-----------+--------------+-----------------+-----------------+
| 1 | 唐僧   | 九环锡杖   | 锦斓袈裟    | 僧鞋      |
| 2 | 孙悟空  | 金箍棒    | 梭子黄金甲   | 藕丝步云履   |
| 3 | 猪八戒  | 九齿钉耙   | 僧衣      | 僧鞋      |
| 4 | 沙僧   | 降妖宝杖   | 僧衣      | 僧鞋      |
+----+-----------+--------------+-----------------+-----------------+

想要得到的数据：

+-----------+-----------+-----------------+
| user_name | equipment | equip_mame   |
+-----------+-----------+-----------------+
| 唐僧   | arms   | 九环锡杖    |
| 唐僧   | clothing | 锦斓袈裟    |
| 唐僧   | shoe   | 僧鞋      |
| 孙悟空  | arms   | 金箍棒     |
| 孙悟空  | clothing | 梭子黄金甲   |
| 孙悟空  | shoe   | 藕丝步云履   |
| 沙僧   | arms   | 降妖宝杖    |
| 沙僧   | clothing | 僧衣      |
| 沙僧   | shoe   | 僧鞋      |
| 猪八戒  | arms   | 九齿钉耙    |
| 猪八戒  | clothing | 僧衣      |
| 猪八戒  | shoe   | 僧鞋      |
+-----------+-----------+-----------------+

union的写法：

select user_name, 'arms' as equipment, arms equip_mame from user1_equipment
union all
select user_name, 'clothing' as equipment, clothing equip_mame from user1_equipment
union all
select user_name, 'shoe' as equipment, shoe equip_mame from user1_equipment
order by 1, 2;

join的写法：

首先看笛卡尔数据集的效果，以唐僧为例

select a.*, b.* from user1_equipment a cross join tb_sequence b where b.id <= 3;

+----+-----------+--------------+-----------------+-----------------+----+
| id | user_name | arms     | clothing    | shoe      | id |
+----+-----------+--------------+-----------------+-----------------+----+
| 1 | 唐僧   | 九环锡杖   | 锦斓袈裟    | 僧鞋      | 1 |
| 1 | 唐僧   | 九环锡杖   | 锦斓袈裟    | 僧鞋      | 2 |
| 1 | 唐僧   | 九环锡杖   | 锦斓袈裟    | 僧鞋      | 3 |
+----+-----------+--------------+-----------------+-----------------+----+

使用case对上面的结果进行处理

select user_name, 
case when b.id = 1 then 'arms' 
when b.id = 2 then 'clothing'
when b.id = 3 then 'shoe' end as equipment,
case when b.id = 1 then arms end arms,
case when b.id = 2 then clothing end clothing,
case when b.id = 3 then shoe end shoe
from user1_equipment a cross join tb_sequence b where b.id <=3;

+-----------+-----------+--------------+-----------------+-----------------+
| user_name | equipment | arms     | clothing    | shoe      |
+-----------+-----------+--------------+-----------------+-----------------+
| 唐僧   | arms   | 九环锡杖   | NULL      | NULL      |
| 唐僧   | clothing | NULL     | 锦斓袈裟    | NULL      |
| 唐僧   | shoe   | NULL     | NULL      | 僧鞋      |
+-----------+-----------+--------------+-----------------+-----------------+

使用coalesce函数将多列数据进行合并

select user_name, 
case when b.id = 1 then 'arms' 
when b.id = 2 then 'clothing'
when b.id = 3 then 'shoe' end as equipment,
coalesce(case when b.id = 1 then arms end,
case when b.id = 2 then clothing end,
case when b.id = 3 then shoe end) equip_mame
from user1_equipment a cross join tb_sequence b where b.id <=3 order by 1, 2;

7. 使用join更新过滤条件中包含自身的表

例子：把同时存在于取经组和悟空朋友圈中的人，在取经组中把comment字段更新为"此人在悟空的朋友圈"

我们很自然地想到先查出user1和user2中user_name都存在的人，然后更新user1表，sql如下

update user1 set comment = '此人在悟空的朋友圈' where user_name in (select a.user_name from user1 a join user2 b on (a.user_name = b.user_name));

很遗憾，上面sql在mysql中报错：ERROR 1093 (HY000): You can't specify target table 'user1' for update in FROM clause，提示不能更新目标表在from子句的表。

那有没有其它办法呢？我们可以将in的写法转换成join的方式

select c.*, d.* from user1 c join (select a.user_name from user1 a join user2 b on (a.user_name = b.user_name)) d on (c.user_name = d.user_name);

+----+-----------+--------------+---------------------------------+-----------+
| id | user_name | comment | mobile | user_name |
+----+-----------+--------------+---------------------------------+-----------+
| 2 | 孙悟空 | 斗战胜佛 | 159384292,022-483432,+86-392432 | 孙悟空 |
+----+-----------+--------------+---------------------------------+-----------+

然后对join之后的视图进行更新即可

update user1 c join (select a.user_name from user1 a join user2 b on (a.user_name = b.user_name)) d on (c.user_name = d.user_name) set c.comment = '此人在悟空的朋友圈';

再查看user1，可以看到user1已修改成功

select * from user1;

+----+-----------+-----------------------------+---------------------------------+
| id | user_name | comment           | mobile             |
+----+-----------+-----------------------------+---------------------------------+
| 1 | 唐僧   | 旃檀功德佛         | 138245623,021-382349      |
| 2 | 孙悟空  | 此人在悟空的朋友圈     | 159384292,022-483432,+86-392432 |
| 3 | 猪八戒  | 净坛使者          | 183208243,055-8234234      |
| 4 | 沙僧   | 金身罗汉          | 293842295,098-2383429      |
| 5 | NULL   | 白龙马           | 993267899            |
+----+-----------+-----------------------------+---------------------------------+

8. 使用join删除重复数据

首先向user2表中插入两条数据

insert into user2(user_name, comment) values ('孙悟空', '美猴王');
insert into user2(user_name, comment) values ('牛魔王', '牛哥');

例子：将user2表中的重复数据删除，只保留id号大的

+----+--------------+-----------+
| id | user_name  | comment  |
+----+--------------+-----------+
| 1 | 孙悟空    | 美猴王  |
| 2 | 牛魔王    | 牛哥   |
| 3 | 铁扇公主   | 牛夫人  |
| 4 | 菩提老祖   | 葡萄   |
| 5 | NULL     | 晶晶   |
| 6 | 孙悟空    | 美猴王  |
| 7 | 牛魔王    | 牛哥   |
+----+--------------+-----------+

首先查看重复记录

select a.*, b.* from user2 a join (select user_name, comment, max(id) id from user2 group by user_name, comment having count(*) > 1) b on (a.user_name=b.user_name and a.comment=b.comment) order by 2;

+----+-----------+-----------+-----------+-----------+------+
| id | user_name | comment  | user_name | comment  | id  |
+----+-----------+-----------+-----------+-----------+------+
| 1 | 孙悟空  | 美猴王  | 孙悟空  | 美猴王  |  6 |
| 6 | 孙悟空  | 美猴王  | 孙悟空  | 美猴王  |  6 |
| 2 | 牛魔王  | 牛哥   | 牛魔王  | 牛哥   |  7 |
| 7 | 牛魔王  | 牛哥   | 牛魔王  | 牛哥   |  7 |
+----+-----------+-----------+-----------+-----------+------+

接着只需要删除(a.id < b.id)的数据即可

delete a from user2 a join (select user_name, comment, max(id) id from user2 group by user_name, comment having count(*) > 1) b on (a.user_name=b.user_name and a.comment=b.comment) where a.id < b.id;

查看user2，可以看到重复数据已经被删掉了

select * from user2;

+----+--------------+-----------+
| id | user_name  | comment  |
+----+--------------+-----------+
| 3 | 铁扇公主   | 牛夫人  |
| 4 | 菩提老祖   | 葡萄   |
| 5 | NULL     | 晶晶   |
| 6 | 孙悟空    | 美猴王  |
| 7 | 牛魔王    | 牛哥   |
+----+--------------+-----------+

总结：

给大家就介绍到这里，大家有兴趣可以多造点数据，然后比较不同的sql写法在执行时间上的区别。本文例子取自于慕课网《sql开发技巧》。

好了，以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对的支持。

更多SQL内容来自木庄网络博客

标签：SQL

返回前面的内容