共計 16047 個字符，預計需要花費 41 分鐘才能閱讀完成。

自動寫代碼機器人，免費開通

怎么在 Mysql 中利用 join 優化 sql？針對這個問題，這篇文章詳細介紹了相對應的分析和解答，希望可以幫助更多想解決這個問題的小伙伴找到更簡單易行的方法。

0. 準備相關表來進行接下來的測試

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 |
+-----------+-----------------+-----------+
|  唐僧  |  旃檀功德佛  | 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;

left join 寫法：

select a.user_name, a.comment, b.comment 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 b.cnt  = 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 |
| 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 

+-----------+-----------+--------------+-----------------+-----------------+
| 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 |  牛魔王  |  牛哥  |
+----+--------------+-----------+

關于怎么在 Mysql 中利用 join 優化 sql 問題的解答就分享到這里了，希望以上內容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關注丸趣 TV 行業資訊頻道了解更多相關知識。

向 AI 問一下細節