主頁(yè) > 知識(shí)庫(kù) > Mysql巧用join優(yōu)化sql的方法詳解

Mysql巧用join優(yōu)化sql的方法詳解
熱門標(biāo)簽：美國(guó)反騷擾電話機(jī)器人 騰訊地圖標(biāo)注提升 福建外呼系統(tǒng)定制化 真人語(yǔ)音電話機(jī)器人 電銷卡外呼系統(tǒng)供應(yīng)商 銅陵防封電銷卡 悟空科技電話機(jī)器人 400電話可以免費(fèi)申請(qǐng)嗎 怎么在地圖標(biāo)注位置生成圖片

0. 準(zhǔn)備相關(guān)表來(lái)進(jìn)行接下來(lái)的測(cè)試

相關(guān)建表語(yǔ)句請(qǐng)看：https://github.com/YangBaohust/my_sql

user1表，取經(jīng)組
+----+-----------+-----------------+---------------------------------+
| id | user_name | comment   | mobile       |
+----+-----------+-----------------+---------------------------------+
| 1 | 唐僧  | 旃檀功德佛  | 138245623,021-382349   |
| 2 | 孫悟空 | 斗戰(zhàn)勝佛  | 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表，取經(jīng)路上殺的妖怪?jǐn)?shù)量
+----+-----------+---------------------+-------+
| 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表，取經(jīng)組裝備
+----+-----------+--------------+-----------------+-----------------+
| id | user_name | arms   | clothing  | shoe   |
+----+-----------+--------------+-----------------+-----------------+
| 1 | 唐僧  | 九環(huán)錫杖  | 錦斕袈裟  | 僧鞋   |
| 2 | 孫悟空 | 金箍棒  | 梭子黃金甲  | 藕絲步云履  |
| 3 | 豬八戒 | 九齒釘耙  | 僧衣   | 僧鞋   |
| 4 | 沙僧  | 降妖寶杖  | 僧衣   | 僧鞋   |
+----+-----------+--------------+-----------------+-----------------+

1. 使用left join優(yōu)化not in子句

例子：找出取經(jīng)組中不屬于悟空朋友圈的人

+----+-----------+-----------------+-----------------------+
| 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寫法：

首先看通過(guò)user_name進(jìn)行連接的外連接數(shù)據(jù)集

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 | 孫悟空 | 斗戰(zhàn)勝佛  | 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表中的所有數(shù)據(jù)都有顯示，b表中的數(shù)據(jù)只有b.user_name與a.user_name相等才顯示，其余都以null值填充，要想找出取經(jīng)組中不屬于悟空朋友圈的人，只需要在b.user_name中加一個(gè)過(guò)濾條件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    |
+----+-----------+-----------------+-----------------------+

看到這里發(fā)現(xiàn)結(jié)果集中還多了一個(gè)白龍馬，繼續(xù)添加過(guò)濾條件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優(yōu)化標(biāo)量子查詢

例子：查看取經(jīng)組中的人在悟空朋友圈的昵稱

+-----------+-----------------+-----------+
| user_name | comment   | comment2 |
+-----------+-----------------+-----------+
| 唐僧  | 旃檀功德佛  | NULL  |
| 孫悟空 | 斗戰(zhàn)勝佛  | 美猴王 |
| 豬八戒 | 凈壇使者  | 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優(yōu)化聚合子查詢

例子：查詢出取經(jīng)組中每人打怪最多的日期

+----+-----------+---------------------+-------+
| 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寫法：

首先看兩表自關(guān)聯(lián)的結(jié)果集，為節(jié)省篇幅，只取豬八戒的打怪?jǐn)?shù)據(jù)來(lái)看

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

可以看到當(dāng)兩表通過(guò)user_name進(jìn)行自關(guān)聯(lián)，只需要對(duì)a表的所有字段進(jìn)行一個(gè)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進(jìn)行分組選擇

例子：對(duì)第3個(gè)例子進(jìn)行升級(jí)，查詢出取經(jīng)組中每人打怪最多的前兩個(gè)日期

+----+-----------+---------------------+-------+
| 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中，可以通過(guò)分析函數(shù)來(lái)實(shí)現(xiàn)

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中報(bào)錯(cuò)ERROR 1064 (42000): You have an error in your SQL syntax; 因?yàn)閙ysql并不支持分析函數(shù)。不過(guò)可以通過(guò)下面的方式去實(shí)現(xiàn)。

首先對(duì)兩表進(jìn)行自關(guān)聯(lián)，為了節(jié)約篇幅，只取出孫悟空的數(shù)據(jù)

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

從上面的表中我們知道孫悟空打怪前兩名的數(shù)量是22和12，那么只需要對(duì)a表的所有字段進(jìn)行一個(gè)group by，對(duì)b表的id做個(gè)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. 使用笛卡爾積關(guān)聯(lián)實(shí)現(xiàn)一列轉(zhuǎn)多行

例子：將取經(jīng)組中每個(gè)電話號(hào)碼變成一行

原始數(shù)據(jù)：

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

想要得到的數(shù)據(jù)：

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

可以看到唐僧有兩個(gè)電話，因此他就需要兩行。我們可以先求出每人的電話號(hào)碼數(shù)量，然后與一張序列表進(jìn)行笛卡兒積關(guān)聯(lián)，為了節(jié)約篇幅，只取出唐僧的數(shù)據(jù)

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對(duì)應(yīng)的就是第幾個(gè)電話號(hào)碼，size就是總的電話號(hào)碼數(shù)量，因此可以加上關(guān)聯(lián)條件(a.id = b.size)，將上面的sql繼續(xù)調(diào)整

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. 使用笛卡爾積關(guān)聯(lián)實(shí)現(xiàn)多列轉(zhuǎn)多行

例子：將取經(jīng)組中每件裝備變成一行

原始數(shù)據(jù)：

+----+-----------+--------------+-----------------+-----------------+
| id | user_name | arms     | clothing    | shoe      |
+----+-----------+--------------+-----------------+-----------------+
| 1 | 唐僧   | 九環(huán)錫杖   | 錦斕袈裟    | 僧鞋      |
| 2 | 孫悟空  | 金箍棒    | 梭子黃金甲   | 藕絲步云履   |
| 3 | 豬八戒  | 九齒釘耙   | 僧衣      | 僧鞋      |
| 4 | 沙僧   | 降妖寶杖   | 僧衣      | 僧鞋      |
+----+-----------+--------------+-----------------+-----------------+

想要得到的數(shù)據(jù)：

+-----------+-----------+-----------------+
| user_name | equipment | equip_mame   |
+-----------+-----------+-----------------+
| 唐僧   | arms   | 九環(huán)錫杖    |
| 唐僧   | 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的寫法：

首先看笛卡爾數(shù)據(jù)集的效果，以唐僧為例

select a.*, b.* from user1_equipment a cross join tb_sequence b where b.id = 3;
+----+-----------+--------------+-----------------+-----------------+----+
| id | user_name | arms     | clothing    | shoe      | id |
+----+-----------+--------------+-----------------+-----------------+----+
| 1 | 唐僧   | 九環(huán)錫杖   | 錦斕袈裟    | 僧鞋      | 1 |
| 1 | 唐僧   | 九環(huán)錫杖   | 錦斕袈裟    | 僧鞋      | 2 |
| 1 | 唐僧   | 九環(huán)錫杖   | 錦斕袈裟    | 僧鞋      | 3 |
+----+-----------+--------------+-----------------+-----------------+----+

使用case對(duì)上面的結(jié)果進(jìn)行處理

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   | 九環(huán)錫杖   | NULL      | NULL      |
| 唐僧   | clothing | NULL     | 錦斕袈裟    | NULL      |
| 唐僧   | shoe   | NULL     | NULL      | 僧鞋      |
+-----------+-----------+--------------+-----------------+-----------------+

使用coalesce函數(shù)將多列數(shù)據(jù)進(jìn)行合并

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更新過(guò)濾條件中包含自身的表

例子：把同時(shí)存在于取經(jīng)組和悟空朋友圈中的人，在取經(jīng)組中把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中報(bào)錯(cuò)：ERROR 1093 (HY000): You can't specify target table 'user1' for update in FROM clause，提示不能更新目標(biāo)表在from子句的表。

那有沒(méi)有其它辦法呢？我們可以將in的寫法轉(zhuǎn)換成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 | 孫悟空 | 斗戰(zhàn)勝佛 | 159384292,022-483432,+86-392432 | 孫悟空 |
+----+-----------+--------------+---------------------------------+-----------+

然后對(duì)join之后的視圖進(jìn)行更新即可

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刪除重復(fù)數(shù)據(jù)

首先向user2表中插入兩條數(shù)據(jù)

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

例子：將user2表中的重復(fù)數(shù)據(jù)刪除，只保留id號(hào)大的

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

首先查看重復(fù)記錄

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

接著只需要?jiǎng)h除(a.id b.id)的數(shù)據(jù)即可

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，可以看到重復(fù)數(shù)據(jù)已經(jīng)被刪掉了

select * from user2;
+----+--------------+-----------+
| id | user_name  | comment  |
+----+--------------+-----------+
| 3 | 鐵扇公主   | 牛夫人  |
| 4 | 菩提老祖   | 葡萄   |
| 5 | NULL     | 晶晶   |
| 6 | 孫悟空    | 美猴王  |
| 7 | 牛魔王    | 牛哥   |
+----+--------------+-----------+

總結(jié)：

給大家就介紹到這里，大家有興趣可以多造點(diǎn)數(shù)據(jù)，然后比較不同的sql寫法在執(zhí)行時(shí)間上的區(qū)別。本文例子取自于慕課網(wǎng)《sql開(kāi)發(fā)技巧》。

好了，以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，謝謝大家對(duì)腳本之家的支持。

您可能感興趣的文章:
  • MySQL聯(lián)表查詢基本操作之left-join常見(jiàn)的坑
  • mysql中l(wèi)eft join設(shè)置條件在on與where時(shí)的用法區(qū)別分析
  • mysql中各種常見(jiàn)join連表查詢實(shí)例總結(jié)
  • MySQL 8.0.18 Hash Join不支持left/right join左右連接問(wèn)題
  • Mysql 8.0.18 hash join測(cè)試(推薦)
  • MySQL 8.0 新特性之哈希連接（Hash Join）
  • MySQL 8.0.18 穩(wěn)定版發(fā)布! Hash Join如期而至
  • MySQL中（JOIN/ORDER BY）語(yǔ)句的查詢過(guò)程及優(yōu)化方法
  • 深入理解mysql的自連接和join關(guān)聯(lián)
  • Mysql join聯(lián)表及id自增實(shí)例解析

標(biāo)簽：武威 云浮 聊城 臨汾 湖南 白銀 湖北 烏海

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《Mysql巧用join優(yōu)化sql的方法詳解》，本文關(guān)鍵詞  Mysql,巧用,join,優(yōu)化,sql,的,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無(wú)關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《Mysql巧用join優(yōu)化sql的方法詳解》相關(guān)的同類信息！
  • 本頁(yè)收集關(guān)于Mysql巧用join優(yōu)化sql的方法詳解的相關(guān)信息資訊供網(wǎng)民參考！
    • 推薦文章