如何删掉表中重复的记录，保留重复记录中ID最小的一条记录？

[99_mei]

如何删掉表中重复的记录，保留重复记录中ID最小的一条记录？

[good2speed]

DECLARE @t TABLE(
id_num INT IDENTITY(1,1) UNIQUE CLUSTERED,
i INT
)

INSERT INTO @t(i)
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 2 UNION ALL
SELECT 3

DELETE @t
FROM @t AS t,(SELECT MIN(id_num) AS id_num,i FROM @t GROUP BY i) AS a
WHERE t.id_num <> a.id_num AND t.i = a.i

/**//* DELETE FROM @t WHERE id_num NOT IN (SELECT MIN(id_num) FROM @t GROUP BY i) */

SELECT * FROM @t

[RainYang]

delete tbl where id in ( select min(id) from tbl group by 重复字段 having count(id)>1)

[RainYang]

sorry,应该是
delete tbl where id in ( select max(id) from tbl group by 重复字段 having count(id)>1)

[RainYang]

delete tbl where id in ( select max(id) from tbl group by 重复字段 having count(*)>1)

[alipaymate]

delete tbl where id not in ( select min(id) from tbl group by 重复字段 having count(*)>1)

[alipaymate]

靠， 我搞错了。