表結(jié)構及數(shù)據(jù)如下
SELECT * FROM test!
test.png
一. 根據(jù)字段查詢表中重復的數(shù)據(jù)
1.根據(jù)字段test1，test2耙替，查詢表中重復的數(shù)據(jù)
SELECT * FROM test a WHERE (a.test1,a.test2) IN (SELECT test1,test2 FROM test GROUP BY test1,test2 HAVING COUNT(*)>1)
1.png
2.根據(jù)字段test1亚侠，test3，查詢表中重復的數(shù)據(jù)
SELECT * FROM test a WHERE (a.test1,a.test3) IN (SELECT test1,test3 FROM test GROUP BY test1,test3 HAVING COUNT(*)>1)
2.png
二.刪除表中重復數(shù)據(jù)俗扇，只保留一條
1.Mysql使用變量方法實現(xiàn)行號功能 (類似于Oracle中的rowid函數(shù))
SELECT @rowid:=@rowid+1 as rowid,test.* FROM (select @rowid:=0) test,test
2.1.png
2.結(jié)合以上方法硝烂，即可實現(xiàn)刪除表中重復數(shù)據(jù)，只保留rowid最小的一行
根據(jù)字段test1铜幽，test3滞谢，查出需要刪除的數(shù)據(jù)rowid：
SELECT * FROM
(SELECT @rowid:=@rowid+1 as rowid,test.* FROM (select @rowid:=0) test,test) c
WHERE rowid not in
(select min(rowid) from (SELECT @rowid:=@rowid+1 as rowid,test.* FROM (select @rowid:=0) test,test)b group by b.test1,b.test3 having count(*)>=1)
2.2.png
根據(jù)字段test1，test3除抛，刪除重復數(shù)據(jù)
DELETE FROM
(SELECT @rowid:=@rowid+1 as rowid,test.* FROM (select @rowid:=0) test,test) t
WHERE rowid not in
(select min(rowid) from (SELECT @rowid:=@rowid+1 as rowid,test.* FROM (select @rowid:=0) test,test)b group by b.test1,b.test3 having count(*)>=1)
尷尬狮杨！設想中的以上寫法為錯誤寫法：原因是：更新數(shù)據(jù)時使用了查詢，而查詢的數(shù)據(jù)又做了更新的條件到忽，mysql不支持這種方式橄教。使用虛擬表的情況下再加一層封裝發(fā)現(xiàn)仍無法規(guī)避這個問題（實體表則可以）？
嘗試多次后喘漏，無奈使用折中解決辦法护蝶，直接創(chuàng)建新表，把去除重復后的數(shù)據(jù)插入新表中翩迈，再刪除舊表持灰；
create temporary table temp1 as SELECT test1,test2,test3,test4 FROM
(SELECT @rowid:=@rowid+1 as rowid,test.* FROM (select @rowid:=0) test,test) t
WHERE rowid in
(select min(rowid) from (SELECT @rowid:=@rowid+1 as rowid,test.* FROM (select @rowid:=0) test,test)b group by b.test1,b.test3 having count(*)>=1)
SELECT * FROM temp1
2.3.png
顯然這個方法存在效率問題，如有更好解決方法請指教负饲；
