需求：刪除表中重復(fù)的數(shù)據(jù)窿给，保留一條記錄速妖，其余的刪除
下面這條SQL語句的功能就是查詢出單表中重復(fù)的數(shù)據(jù)并刪除
DELETE FROM wz_outpatient WHERE id in(SELECT a.id FROM
(SELECT id FROM wz_outpatient WHERE (SFXMDM, KSDM,RQ,HJJE )
IN ( SELECT SFXMDM, KSDM,RQ,HJJE FROM wz_outpatient GROUP BY SFXMDM, KSDM,RQ,HJJE HAVING count( * ) > 1 )
AND id NOT IN ( SELECT min( id ) FROM wz_outpatient
GROUP BY SFXMDM, KSDM,RQ,HJJE HAVING count( * ) > 1 )) a);
咱們現(xiàn)在開始一步步的拆解這段語句
-
step1
SELECT SFXMDM, KSDM,RQ,HJJE FROM wz_outpatient GROUP BY SFXMDM, KSDM,RQ,HJJE HAVING count( * ) > 1
這最里層的SQL語句中，咱們根據(jù)一些條件查詢了wz_outpatient表中的幾個字段值沐扳，并加上了Having篩選了數(shù)據(jù)重復(fù)條目大于1的結(jié)果症脂。
-
step2
SELECT id FROM wz_outpatient WHERE (SFXMDM, KSDM,RQ,HJJE )
IN ( 上例)
AND id NOT IN ( SELECT min( id ) FROM wz_outpatient
GROUP BY SFXMDM, KSDM,RQ,HJJE HAVING count( * ) > 1 )
這個SQL語句里面有兩個查詢條件
- 將上例的結(jié)果作為這次語句的
in條件查詢id -
id在第二個條件中將重復(fù)數(shù)據(jù)查詢返回了之后,篩選出除了id最小之外的其他數(shù)據(jù)
PS: 也可用MAX()功能查詢除了id最大的之外的結(jié)果集
這一步驟的結(jié)果就是查詢了該表中的除了最小id之外的重復(fù)數(shù)據(jù)
-
step3
SELECT a.id FROM (上例) a
邏輯上其實可以忽略這一步驟直接拼接delete語句就可刪除重復(fù)數(shù)據(jù)并保留一條id最小的數(shù)據(jù).但是在MySQL中執(zhí)行的話,會報如下錯誤
image.png
大致意思就是不可在直接查詢表又對表進行更新
所以我們就加了step3,將查詢結(jié)果做出中間過渡表a
-
step4
DELETE FROM wz_outpatient WHERE id in(SELECT a.id FROM
(SELECT id FROM wz_outpatient WHERE (SFXMDM, KSDM,RQ,HJJE )
IN ( SELECT SFXMDM, KSDM,RQ,HJJE FROM wz_outpatient GROUP BY SFXMDM, KSDM,RQ,HJJE HAVING count( * ) > 1 )
AND id NOT IN ( SELECT min( id ) FROM wz_outpatient
GROUP BY SFXMDM, KSDM,RQ,HJJE HAVING count( * ) > 1 )) a);
執(zhí)行SQL,親測有效??
如有疑問可評論,喜歡請like一下??
