Mysql巧用join优化sql的方法详解

0. 准备相关表来进行接下来的测试

相关建表语句请看：https://github.com/YangBaohust/my_sql

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      |+----+-----------+-----------------+---------------------------------+user2表，悟空的朋友圈+----+--------------+-----------+| id | user_name | comment |+----+--------------+-----------+| 1 | 孙悟空  | 美猴王 || 2 | 牛魔王  | 牛哥  || 3 | 铁扇公主  | 牛夫人 || 4 | 菩提老祖  | 葡萄  || 5 | NULL   | 晶晶  |+----+--------------+-----------+user1_kills表，取经路上杀的妖怪数量+----+-----------+---------------------+-------+| id | user_name | timestr    | kills |+----+-----------+---------------------+-------+| 1 | 孙悟空 | 2013-01-10 00:00:00 | 10 || 2 | 孙悟空 | 2013-02-01 00:00:00 |  2 || 3 | 孙悟空 | 2013-02-05 00:00:00 | 12 || 4 | 孙悟空 | 2013-02-12 00:00:00 | 22 || 5 | 猪八戒 | 2013-01-11 00:00:00 | 20 || 6 | 猪八戒 | 2013-02-07 00:00:00 | 17 || 7 | 猪八戒 | 2013-02-08 00:00:00 | 35 || 8 | 沙僧  | 2013-01-10 00:00:00 |  3 || 9 | 沙僧  | 2013-01-22 00:00:00 |  9 || 10 | 沙僧  | 2013-02-11 00:00:00 |  5 |+----+-----------+---------------------+-------+user1_equipment表，取经组装备+----+-----------+--------------+-----------------+-----------------+| id | user_name | arms   | clothing  | shoe   |+----+-----------+--------------+-----------------+-----------------+| 1 | 唐僧  | 九环锡杖  | 锦斓袈裟  | 僧鞋   || 2 | 孙悟空 | 金箍棒  | 梭子黄金甲  | 藕丝步云履  || 3 | 猪八戒 | 九齿钉耙  | 僧衣   | 僧鞋   || 4 | 沙僧  | 降妖宝杖  | 僧衣   | 僧鞋   |+----+-----------+--------------+-----------------+-----------------+

例子：找出取经组中不属于悟空朋友圈的人

+----+-----------+-----------------+-----------------------+| id | user_name | comment   | mobile    |+----+-----------+-----------------+-----------------------+| 1 | 唐僧  | 旃檀功德佛  | 138245623,021-382349 || 3 | 猪八戒 | 净坛使者  | 183208243,055-8234234 || 4 | 沙僧  | 金身罗汉  | 293842295,098-2383429 |+----+-----------+-----------------+-----------------------+

select * from user1 a where a.user_name not in (select user_name from user2 where user_name is not null);

首先看通过user_name进行连接的外连接数据集

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

select a.* from user1 a left join user2 b on (a.user_name = b.user_name) where b.user_name is null;

select a.* from user1 a left join user2 b on (a.user_name = b.user_name) where b.user_name is null and a.user_name is not null;

2. 使用left join优化标量子查询

例子：查看取经组中的人在悟空朋友圈的昵称

+-----------+-----------------+-----------+| user_name | comment   | comment2 |+-----------+-----------------+-----------+| 唐僧  | 旃檀功德佛  | NULL  || 孙悟空 | 斗战胜佛  | 美猴王 || 猪八戒 | 净坛使者  | NULL  || 沙僧  | 金身罗汉  | NULL  || NULL  | 白龙马   | NULL  |+-----------+-----------------+-----------+

select a.user_name, a.comment, (select comment from user2 b where b.user_name = a.user_name) comment2 from user1 a;

select a.user_name, a.comment, b.comment comment2 from user1 a left join user2 b on (a.user_name = b.user_name);

例子：查询出取经组中每人打怪最多的日期

+----+-----------+---------------------+-------+| id | user_name | timestr    | kills |+----+-----------+---------------------+-------+| 4 | 孙悟空 | 2013-02-12 00:00:00 | 22 || 7 | 猪八戒 | 2013-02-08 00:00:00 | 35 || 9 | 沙僧  | 2013-01-22 00:00:00 |  9 |+----+-----------+---------------------+-------+

select * from user1_kills a where a.kills = (select max(b.kills) from user1_kills b where b.user_name = a.user_name);

首先看两表自关联的结果集，为节省篇幅，只取猪八戒的打怪数据来看

select a.*, b.* from user1_kills a join user1_kills b on (a.user_name = b.user_name) order by 1;

select a.* from user1_kills a join user1_kills b on (a.user_name = b.user_name) group by a.id, a.user_name, a.timestr, a.kills having a.kills = max(b.kills);

例子：对第3个例子进行升级，查询出取经组中每人打怪最多的前两个日期

+----+-----------+---------------------+-------+| id | user_name | timestr       | kills |+----+-----------+---------------------+-------+| 3 | 孙悟空  | 2013-02-05 00:00:00 |  12 || 4 | 孙悟空  | 2013-02-12 00:00:00 |  22 || 5 | 猪八戒  | 2013-01-11 00:00:00 |  20 || 7 | 猪八戒  | 2013-02-08 00:00:00 |  35 || 9 | 沙僧   | 2013-01-22 00:00:00 |   9 || 10 | 沙僧   | 2013-02-11 00:00:00 |   5 |+----+-----------+---------------------+-------+

select b.* from (select a.*, row_number() over(partition by user_name order by kills desc) cnt from user1_kills a) b where b.cnt <= 2;

首先对两表进行自关联，为了节约篇幅，只取出孙悟空的数据

select a.*, b.* from user1_kills a join user1_kills b on (a.user_name=b.user_name and a.kills<=b.kills) order by a.user_name, a.kills desc;

select a.* from user1_kills a join user1_kills b on (a.user_name=b.user_name and a.kills<=b.kills) group by a.id, a.user_name, a.timestr, a.kills having count(b.id) <= 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;

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);

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

原始数据：

+----+-----------+--------------+-----------------+-----------------+| 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   | 僧鞋      |+-----------+-----------+-----------------+

select user_name, 'arms' as equipment, arms equip_mame from user1_equipmentunion allselect user_name, 'clothing' as equipment, clothing equip_mame from user1_equipmentunion allselect user_name, 'shoe' as equipment, shoe equip_mame from user1_equipmentorder by 1, 2;

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

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

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 shoefrom user1_equipment a cross join tb_sequence b where b.id <=3;

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_mamefrom user1_equipment a cross join tb_sequence b where b.id <=3 order by 1, 2;

例子：把同时存在于取经组和悟空朋友圈中的人，在取经组中把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));

那有没有其它办法呢？我们可以将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);

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 = '此人在悟空的朋友圈';

select * from user1;

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

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

+----+--------------+-----------+| 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;

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;

select * from user2;

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

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

更多相关文章

随机推荐