一、關(guān)于mysql表中數(shù)據(jù)重復(fù)

關(guān)于刪除mysql表中重復(fù)數(shù)據(jù)問(wèn)題，本文中給到兩種辦法：聚合函數(shù)、窗口函數(shù)row_number()的方法。
(注意：MySQL從8.0開(kāi)始支持窗口函數(shù))

測(cè)試數(shù)據(jù)準(zhǔn)備：首先創(chuàng)建一個(gè)測(cè)試表test，插入一些測(cè)試數(shù)據(jù)，模擬一些重復(fù)數(shù)據(jù)（最終目標(biāo)：刪除重復(fù)數(shù)據(jù)，但不處理null行）

先查詢(xún)下重復(fù)數(shù)據(jù)，確認(rèn)待處理數(shù)據(jù)的數(shù)量，然后開(kāi)始處理：

SELECTseq_id,out_user_code,COUNT( out_user_code ) count
FROMtest
WHEREis_deleted = 0AND out_user_code IS NOT NULL
GROUP BYout_user_code
HAVINGcount( out_user_code )> 1

二、聚合函數(shù)min(id)+not in

思路：首先通過(guò)子查詢(xún)?nèi)〕?id 最小的不重復(fù)行,然后通過(guò) not in 刪除重復(fù)數(shù)據(jù)

1、首先查詢(xún)一下 id 最小的不重復(fù)行（我們留下最早插入的數(shù)據(jù)，后面的重復(fù)數(shù)據(jù)都刪除）:

SELECTmin(seq_id) seq_id,out_user_code,COUNT( out_user_code ) count 
FROMtest 
GROUP BYout_user_code 

2、通過(guò)查詢(xún)結(jié)果可知，重復(fù)的數(shù)據(jù)行seq_id為2、7的數(shù)據(jù)過(guò)濾掉了，接下來(lái)NOT IN 操作應(yīng)該刪除2、7重復(fù)數(shù)據(jù)行。那按照假設(shè)想法執(zhí)行NOT IN：

DELETE from test where r.seq_id not in (SELECTmin(t.seq_id) seq_idFROMtest  tGROUP BYt.out_user_code
)  r

會(huì)發(fā)現(xiàn)報(bào)錯(cuò)：

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'r' at line 8, Time: 0.007000s

原因：不能先select出同一表中的某些值，再u(mài)pdate這個(gè)表(在同一語(yǔ)句中)，即不能依據(jù)某字段值做判斷再來(lái)更新某字段的值。

解決方案：可將SELECT出的結(jié)果再通過(guò)中間表SELECT一遍。

3、最終處理sql：

DELETE from test where seq_id not in (SELECT r.seq_id from (SELECTmin(t.seq_id) seq_idFROMtest  tGROUP BYt.out_user_code)  r 
) and out_user_code is not null

換種寫(xiě)法（保證相關(guān)字段有索引）：

DELETE from test 
where
out_user_code in (select * from (select out_user_code from test del group by out_user_code HAVING count(out_user_code) >1)a)
and seq_id not in(select * from (select min(seq_id) id from test del group by out_user_code  HAVING count(out_user_code) >1)b
)f

提醒：能邏輯刪除盡量不要物理刪除。

二、窗口函數(shù)row_number()

思路：通過(guò) PARTITION BY 對(duì)列進(jìn)行分區(qū)排序并生成序號(hào)列,然后將序號(hào)大于 1 的行刪除,row_number() over partition by。

1、分區(qū)查詢(xún)：

SELECTROW_NUMBER() OVER ( PARTITION BY out_user_code ORDER BY seq_id ) num,out_user_code 
FROMtest 
WHEREout_user_code IS NOT NULL

知識(shí)補(bǔ)充：
1、ROW_NUMBER:對(duì)結(jié)果集的輸出進(jìn)行編號(hào)，是運(yùn)行查詢(xún)時(shí)計(jì)算出的臨時(shí)值。 具體來(lái)說(shuō)，返回結(jié)果集分區(qū)內(nèi)行的序列號(hào)，每個(gè)分區(qū)的第一行從 1 開(kāi)始。
2、ROW_NUMBER() 具有不確定性。除非以下條件成立，否則不保證在每次執(zhí)行時(shí)，使用 ROW_NUMBER() 的查詢(xún)所返回行的順序都完全相同。
1）分區(qū)列的值是唯一的。
2）ORDER BY 列的值是唯一的。
3）分區(qū)列和 ORDER BY 列的值的組合是唯一的。

2、直接嘗試刪除num>1的數(shù)據(jù)：

DELETE a 
FROM (SELECTROW_NUMBER() OVER (PARTITION BY out_user_code ORDER BY seq_id) num FROM test where out_user_code IS NOT NULL
) a 
WHERE num>1

會(huì)發(fā)現(xiàn)報(bào)錯(cuò)：
1288 - The target table a of the DELETE is not updatable, Time: 0.007000s

原因同上，同樣的我們換個(gè)方式處理一下。給窗口指定別名：WINDOW w AS (PARTITION BY 字段1 ORDER BY 字段2)

3、最終處理sql:

DELETE
FROM test
WHERE seq_id in (SELECT seq_idFROM(SELECT *FROM (SELECT ROW_NUMBER() OVER w AS row_num,seq_idFROM test  where out_user_code is not nullWINDOW w AS (PARTITION BY out_user_code ORDER BY seq_id))tWHERE row_num >1)e
)

四、補(bǔ)充：常見(jiàn)的窗口函數(shù)

注：‘參數(shù)’列說(shuō)明該函數(shù)是否可以加參數(shù)。“否”說(shuō)明該函數(shù)的括號(hào)內(nèi)不可以加參數(shù)。
expr即可以代表字段，也可以代表在字段上的計(jì)算，比如sum(col)等。

窗口函數(shù)的一個(gè)概念是當(dāng)前行，當(dāng)前行屬于某個(gè)窗口，窗口由over關(guān)鍵字用來(lái)指定函數(shù)執(zhí)行的窗口范圍，如果后面括號(hào)中什么都不寫(xiě)，則意味著窗口包含滿(mǎn)足where條件的所有行，窗口函數(shù)基于所有行進(jìn)行計(jì)算；如果不為空，則有三個(gè)參數(shù)來(lái)設(shè)置窗口：

• partition by子句：按照指定字段進(jìn)行分區(qū)，兩個(gè)分區(qū)由邊界分隔，窗口函數(shù)在不同的分區(qū)內(nèi)分別執(zhí)行，在跨越分區(qū)邊界時(shí)重新初始化。
• order by子句：按照指定字段進(jìn)行排序，窗口函數(shù)將按照排序后的記錄順序進(jìn)行編號(hào)。可以和partition by子句配合使用，也可以單獨(dú)使用。
• frame子句：當(dāng)前分區(qū)的一個(gè)子集，用來(lái)定義子集的規(guī)則，通常用來(lái)作為滑動(dòng)窗口使用。