如何在MSSQL删除多条重复数据
id | ColA | ColB |
1 | 12345 | ABCD |
2 |
12345 |
ABCD |
关键是找出重复的其中的一条,
如下:
SELECT max(id) as id, a.cola,a.colB
FROM a join
(
SELECT cola,colb FROM a GROUP BY cola, colb
HAVING COUNT(*) > 1) b
on a.filecode = b.filecode and a.colb=b.colb
group by a.col1,a.colB
FROM a join
(
SELECT cola,colb FROM a GROUP BY cola, colb
HAVING COUNT(*) > 1) b
on a.filecode = b.filecode and a.colb=b.colb
group by a.col1,a.colB
然后删除即可.