SQL查询表中重复记录

---------------------查询重复记录  正式库 T_DRP_ZD_KC_HPSXJXX_20140108

--重复的所有信息
SELECT * FROM T_DRP_ZD_KC_HPSXJXX A
WHERE (A.wldwxxid,A.Wlxxid)
IN (SELECT A.Wldwxxid,A.Wlxxid FROM T_DRP_ZD_KC_HPSXJXX A GROUP BY A.Wldwxxid,A.Wlxxid HAVING(COUNT(A.Hpsxjxxid))>1)

SELECT * FROM T_DRP_ZD_KC_HPSXJXX_20140108 A
WHERE (A.wldwxxid,A.Wlxxid) IN (SELECT A.Wldwxxid,A.Wlxxid FROM T_DRP_ZD_KC_HPSXJXX_20140108 A GROUP BY A.Wldwxxid,A.Wlxxid HAVING(COUNT(A.Hpsxjxxid))>1)

---查多余的重复信息
SELECT * FROM T_DRP_ZD_KC_HPSXJXX_20140108 A
WHERE (A.wldwxxid,A.Wlxxid) IN (SELECT A.Wldwxxid,A.Wlxxid FROM T_DRP_ZD_KC_HPSXJXX_20140108 A GROUP BY A.Wldwxxid,A.Wlxxid HAVING(COUNT(A.Hpsxjxxid))>1)
AND HPSXJXXID NOT IN (SELECT MIN(A.HPSXJXXID) FROM T_DRP_ZD_KC_HPSXJXX_20140108 A GROUP BY A.Wldwxxid,A.Wlxxid HAVING(COUNT(A.Hpsxjxxid))>1)

SELECT * FROM T_DRP_ZD_KC_HPSXJXX_20140108 A
WHERE (A.wldwxxid,A.Wlxxid) IN (SELECT A.Wldwxxid,A.Wlxxid FROM T_DRP_ZD_KC_HPSXJXX_20140108 A GROUP BY A.Wldwxxid,A.Wlxxid HAVING(COUNT(A.Hpsxjxxid))>1)
AND ROWID NOT IN (select min(rowid) from T_DRP_ZD_KC_HPSXJXX_20140108 A group by A.Wldwxxid,A.Wlxxid HAVING(COUNT(A.Hpsxjxxid))>1)
;

----查询正常的单条信息
SELECT * FROM T_DRP_ZD_KC_HPSXJXX_20140108 A
WHERE (A.wldwxxid,A.Wlxxid) IN (SELECT A.Wldwxxid,A.Wlxxid FROM T_DRP_ZD_KC_HPSXJXX_20140108 A GROUP BY A.Wldwxxid,A.Wlxxid HAVING(COUNT(A.Hpsxjxxid))>1)
AND HPSXJXXID IN (SELECT MIN(A.HPSXJXXID) FROM T_DRP_ZD_KC_HPSXJXX_20140108 A GROUP BY A.Wldwxxid,A.Wlxxid HAVING(COUNT(A.Hpsxjxxid))>1)

SELECT * FROM T_DRP_ZD_KC_HPSXJXX_20140108 A
WHERE (A.wldwxxid,A.Wlxxid) IN (SELECT A.Wldwxxid,A.Wlxxid FROM T_DRP_ZD_KC_HPSXJXX_20140108 A GROUP BY A.Wldwxxid,A.Wlxxid HAVING(COUNT(A.Hpsxjxxid))>1)
AND ROWID IN (select min(rowid) from T_DRP_ZD_KC_HPSXJXX_20140108 A group by A.Wldwxxid,A.Wlxxid HAVING(COUNT(A.Hpsxjxxid))>1)
;

----删除多余的重复信息
DELETE FROM T_DRP_ZD_KC_HPSXJXX_20140108 A
WHERE (A.wldwxxid,A.Wlxxid) IN (SELECT A.Wldwxxid,A.Wlxxid FROM T_DRP_ZD_KC_HPSXJXX_20140108 A GROUP BY A.Wldwxxid,A.Wlxxid HAVING(COUNT(A.Hpsxjxxid))>1)
AND ROWID NOT IN (select min(rowid) from T_DRP_ZD_KC_HPSXJXX_20140108 A group by A.Wldwxxid,A.Wlxxid HAVING(COUNT(A.Hpsxjxxid))>1)
;
DELETE FROM T_DRP_ZD_KC_HPSXJXX_20140108 A
WHERE (A.wldwxxid,A.Wlxxid) IN (SELECT A.Wldwxxid,A.Wlxxid FROM T_DRP_ZD_KC_HPSXJXX_20140108 A GROUP BY A.Wldwxxid,A.Wlxxid HAVING(COUNT(A.Hpsxjxxid))>1)
AND HPSXJXXID NOT IN (SELECT MIN(A.HPSXJXXID) FROM T_DRP_ZD_KC_HPSXJXX_20140108 A GROUP BY A.Wldwxxid,A.Wlxxid HAVING(COUNT(A.Hpsxjxxid))>1)