mysql 整行去重_sql去重(整行或按列都行)

--author:coffeesweet

--查出所有重复的数据

SELECT ROWID,NCA.CLASS_NAME FROM NG_CFG_AUDIT NCA WHERE NCA.CLASS_NAME IN

(SELECT NCA2.CLASS_NAME FROM NG_CFG_AUDIT NCA2 GROUP BY NCA2.CLASS_NAME HAVING COUNT(*)>1);

--查询多余的数据的rowid最大的一条

SELECT MAX(NCA3.ROWID),NCA3.CLASS_NAME FROM NG_CFG_AUDIT NCA3 GROUP BY NCA3.CLASS_NAME HAVING COUNT(*)>1;

--查询多出来的数据

(

SELECT NCA.ROWID,NCA.CLASS_NAME FROM NG_CFG_AUDIT NCA WHERE NCA.CLASS_NAME IN

(SELECT NCA2.CLASS_NAME FROM NG_CFG_AUDIT NCA2 GROUP BY NCA2.CLASS_NAME HAVING COUNT(*)>1)

)

MINUS

(

SELECT MAX(NCA3.ROWID),NCA3.CLASS_NAME FROM NG_CFG_AUDIT NCA3 GROUP BY NCA3.CLASS_NAME HAVING COUNT(*)>1

);

--删除多余的数据(对于多的只保留一条)

DELETE  FROM NG_CFG_AUDIT NCA4 WHERE NCA4.ROWID IN(

(

SELECT NCA.ROWID FROM NG_CFG_AUDIT NCA WHERE NCA.CLASS_NAME IN

(SELECT NCA2.CLASS_NAME FROM NG_CFG_AUDIT NCA2 GROUP BY NCA2.CLASS_NAME HAVING COUNT(*)>1)

)

MINUS

(

SELECT MAX(NCA3.ROWID) FROM NG_CFG_AUDIT NCA3 GROUP BY NCA3.CLASS_NAME HAVING COUNT(*)>1

)

);

--普通查询语句

SELECT ROWID,NCA.* FROM NG_CFG_AUDIT NCA WHERE NCA.CLASS_NAME='com.ersoft.service.impl.PopularizeCostDrawInfoSVImpl';

分享到：

2009-09-28 16:40

浏览 2860

评论