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';