Mysql-巧⽤join来优化sql
0. 准备相关表来进⾏接下来的测试
相关建表语句请看：github/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 | 沙僧      | 降妖宝杖    | 僧⾐            | 僧鞋            |
+----+-----------+--------------+-----------------+-----------------+
1. 使⽤left join优化not in⼦句
例⼦：出取经组中不属于悟空朋友圈的⼈
+----+-----------+-----------------+-----------------------+
| id | user_name | comment        | mobile                |
+----+-----------+-----------------+-----------------------+
|  1 | 唐僧      | 旃檀功德佛      | 138245623,021-382349  |
|  3 | 猪⼋戒    | 净坛使者        | 183208243,055-8234234 |
|  4 | 沙僧      | ⾦⾝罗汉        | 293842295,098-2383429 |
+----+-----------+-----------------+-----------------------+
not in写法：
select * from user1 a where a.user_name not in (select user_name from user2 where user_name is not null);
left join写法：
⾸先看通过user_name进⾏连接的外连接数据集
select a.*, b.* from user1 a left join user2 b on (a.user_name = b.user_name);
+----+-----------+-----------------+---------------------------------+------+-----------+-----------+
| id | user_name | comment        | mobile                          | id  | user_name | comment  |
+----+-----------+-----------------+---------------------------------+------+-----------+-----------+
|  2 | 孙悟空    | ⽃战胜佛        | 159384292,022-483432,+86-392432 |    1 | 孙悟空    | 美猴王    |
|  1 | 唐僧      | 旃檀功德佛      | 138245623,021-382349            | NULL | NULL      | NULL      |
|  3 | 猪⼋戒    | 净坛使者        | 183208243,055-8234234          | NULL | NULL      | NULL      |
|  4 | 沙僧      | ⾦⾝罗汉        | 293842295,098-2383429          | NULL | NULL      | NULL      |
|  5 | NULL      | ⽩龙马          | 993267899                      | NULL | NULL      | NULL      |
+----+-----------+-----------------+---------------------------------+------+-----------+-----------+
可以看到a表中的所有数据都有显⽰，b表中的数据只有b.user_name与a.user_name相等才显⽰，其余都以null值填充，要想出取经组中不属于悟空朋友圈的⼈，只需要在b.user_name中加⼀个过滤条件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;
+----+-----------+-----------------+-----------------------+
| id | user_name | comment        | mobile                |
+----+-----------+-----------------+-----------------------+
|  1 | 唐僧      | 旃檀功德佛      | 138245623,021-382349  |
|  3 | 猪⼋戒    | 净坛使者        | 183208243,055-8234234 |
|  4 | 沙僧      | ⾦⾝罗汉        | 293842295,098-2383429 |
|  5 | NULL      | ⽩龙马          | 993267899            |
+----+-----------+-----------------+-----------------------+
看到这⾥发现结果集中还多了⼀个⽩龙马，继续添加过滤条件a.user_name is not 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  |
+-----------+-----------------+-----------+
| 唐僧      | 旃檀功德佛      |      |
| 孙悟空    | ⽃战胜佛        | 美猴王    |
| 猪⼋戒    | 净坛使者        |      |
| 沙僧      | ⾦⾝罗汉        |      |
|      | ⽩龙马          |      |
+-----------+-----------------+-----------+
⼦查询写法：
select a.user_name, ament, (select comment from user2 b where b.user_name = a.user_name) comment2 from user1 a;
left join写法：
select a.user_name, ament, bment comment2 from user1 a left join user2 b on (a.user_name = b.user_name);
3. 使⽤join优化聚合⼦查询
例⼦：查询出取经组中每⼈打怪最多的⽇期
+----+-----------+---------------------+-------+
| 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);
join写法：
⾸先看两表⾃关联的结果集，为节省篇幅，只取猪⼋戒的打怪数据来看
select a.*, b.* from user1_kills a join user1_kills b on (a.user_name = b.user_name) order by 1;
+----+-----------+---------------------+-------+----+-----------+---------------------+-------+
| id | user_name | timestr            | kills | id | user_name | timestr            | kills |
+----+-----------+---------------------+-------+----+-----------+---------------------+-------+
|  5 | 猪⼋戒    | 2013-01-11 00:00:00 |    20 |  5 | 猪⼋戒    | 2013-01-11 00:00:00 |    20 |
|  5 | 猪⼋戒    | 2013-01-11 00:00:00 |    20 |  6 | 猪⼋戒    | 2013-02-07 00:00:00 |    17 |
|  5 | 猪⼋戒    | 2013-01-11 00:00:00 |    20 |  7 | 猪⼋戒    | 2013-02-08 00:00:00 |    35 |
|  6 | 猪⼋戒    | 2013-02-07 00:00:00 |    17 |  7 | 猪⼋戒    | 2013-02-08 00:00:00 |    35 |
|  6 | 猪⼋戒    | 2013-02-07 00:00:00 |    17 |  5 | 猪⼋戒    | 2013-01-11 00:00:00 |    20 |
|  6 | 猪⼋戒    | 2013-02-07 00:00:00 |    17 |  6 | 猪⼋戒    | 2013-02-07 00:00:00 |    17 |
|  7 | 猪⼋戒    | 2013-02-08 00:00:00 |    35 |  5 | 猪⼋戒    | 2013-01-11 00:00:00 |    20 |
|  7 | 猪⼋戒    | 2013-02-08 00:00:00 |    35 |  6 | 猪⼋戒    | 2013-02-07 00:00:00 |    17 |
|  7 | 猪⼋戒    | 2013-02-08 00:00:00 |    35 |  7 | 猪⼋戒    | 2013-02-08 00:00:00 |    35 |
+----+-----------+---------------------+-------+----+-----------+---------------------+-------+
可以看到当两表通过user_name进⾏⾃关联，只需要对a表的所有字段进⾏⼀个group by，取b表中的max(kills)，只要
a.kills=max(
b.kills)就满⾜要求了。sql如下
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);
4. 使⽤join进⾏分组选择
例⼦：对第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 |
+----+-----------+---------------------+-------+
在oracle中，可以通过分析函数来实现
select b.* from (select a.*, row_number() over(partition by user_name order by kills desc) cnt from user1_kills a) b where bt <= 2;
很遗憾，上⾯sql在mysql中报错ERROR 1064 (42000): You have an error in your SQL syntax; 因为mysql并不⽀持分析函数。不过可以通过下⾯的⽅式去实现。
⾸先对两表进⾏⾃关联，为了节约篇幅，只取出孙悟空的数据
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;
+----+-----------+---------------------+-------+----+-----------+---------------------+-------+
| id | user_name | timestr            | kills | id | user_name | timestr            | kills |
+----+-----------+---------------------+-------+----+-----------+---------------------+-------+
|  4 | 孙悟空    | 2013-02-12 00:00:00 |    22 |  4 | 孙悟空    | 2013-02-12 00:00:00 |    22 |
|  3 | 孙悟空    | 2013-02-05 00:00:00 |    12 |  3 | 孙悟空    | 2013-02-05 00:00:00 |    12 |
|  3 | 孙悟空    | 2013-02-05 00:00:00 |    12 |  4 | 孙悟空    | 2013-02-12 00:00:00 |    22 |
|  1 | 孙悟空    | 2013-01-10 00:00:00 |    10 |  1 | 孙悟空    | 2013-01-10 00:00:00 |    10 |
|  1 | 孙悟空    | 2013-01-10 00:00:00 |    10 |  3 | 孙悟空    | 2013-02-05 00:00:00 |    12 |
|  1 | 孙悟空    | 2013-01-10 00:00:00 |    10 |  4 | 孙悟空    | 2013-02-12 00:00:00 |    22 |
|  2 | 孙悟空    | 2013-02-01 00:00:00 |    2 |  1 | 孙悟空    | 2013-01-10 00:00:00 |    10 |
|  2 | 孙悟空    | 2013-02-01 00:00:00 |    2 |  3 | 孙悟空    | 2013-02-05 00:00:00 |    12 |
|  2 | 孙悟空    | 2013-02-01 00:00:00 |    2 |  4 | 孙悟空    | 2013-02-12 00:00:00 |    22 |
|  2 | 孙悟空    | 2013-02-01 00:00:00 |    2 |  2 | 孙悟空    | 2013-02-01 00:00:00 |    2 |
+----+-----------+---------------------+-------+----+-----------+---------------------+-------+
从上⾯的表中我们知道孙悟空打怪前两名的数量是22和12，那么只需要对a表的所有字段进⾏⼀个group by，对b表的id做个
count，count值⼩于等于2就满⾜要求，sql改写如下：
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;
5. 使⽤笛卡尔积关联实现⼀列转多⾏
例⼦：将取经组中每个电话号码变成⼀⾏
原始数据：
+-----------+---------------------------------+
| user_name | mobile                          |
+-----------+---------------------------------+
| 唐僧      | 138245623,021-382349            |
| 孙悟空    | 159384292,022-483432,+86-392432 |
| 猪⼋戒    | 183208243,055-8234234          |
| 沙僧      | 293842295,098-2383429          |
|      | 993267899                      |
+-----------+---------------------------------+
想要得到的数据：
+-----------+-------------+
| user_name | mobile      |
+-----------+-------------+
| 唐僧      | 138245623  |
| 唐僧      | 021-382349  |
| 孙悟空    | 159384292  |
| 孙悟空    | 022-483432  |
| 孙悟空    | +86-392432  |
| 猪⼋戒    | 183208243  |
| 猪⼋戒    | 055-8234234 |
| 沙僧      | 293842295  |
| 沙僧      | 098-2383429 |
|      | 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                          |  |
+----+-----------+---------------------------------+------+
|  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_bile, ',', 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  | 僧⾐            |join的四种用法
| 猪⼋戒    | 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      | 九环锡杖    |            |            |
| 唐僧      | clothing  |          | 锦斓袈裟        |            |
| 唐僧      | shoe      |          |            | 僧鞋            |
+-----------+-----------+--------------+-----------------+-----------------+

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。