用于搜索重复多个列上的行的查询 [英] Query for searching duplicates rows on multiple columns

问题描述

需要在多个列中查找具有重复数据的行

Need to find the rows with duplicates data in a multiple columns

hID     sid    lid    aid    did     shid    soid         date
------------------------------------------------------------------
0       123    jkn    rbr    tm      asd     hjk       10/11/2008
1       563    kjh    jkh    dfsd    we      hjk       11/12/2010
2       131    jklj   jkjk   adsd    vcv     asd       01/01/2012
3       656    hjkh   hjkh   dasda   fas     fads      03/26/2012
4       123    jkn    rbr    tm      asd     hjk       09/09/2012

应该能够返回喜欢一个。

Should be able to return like dis one..

hID     sid    lid    aid    did     shid    soid         date
------------------------------------------------------------------
0       123    jkn    rbr    tm      asd     hjk       10/11/2008
4       123    jkn    rbr    tm      asd     hjk       09/09/2012

任何想法？提前感谢！

推荐答案

下面的查询比@ TimSchmelter的解决方案重复得多，如果你碰巧在 sid，lid，aid，did，shid，soid 上有索引，则重复次数相对较少。

The query below is much more repetitive than @TimSchmelter's solution, but yields a measurable performance advantage for large tables with relatively few duplicates if you happen to have an index on sid, lid, aid, did, shid, soid.

SELECT t.* FROM t INNER JOIN (
  SELECT sid, lid, aid, did, shid, soid, COUNT(1) AS count FROM t
  GROUP BY sid, lid, aid, did, shid, soid HAVING COUNT(1) > 1
) tt ON t.sid=tt.sid AND t.lid=tt.lid AND t.aid=tt.aid AND t.did=tt.did AND t.shid=tt.shid AND t.soid=tt.soid;

这篇关于用于搜索重复多个列上的行的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！