如何删除表中重复的数据

[littlebirds]

如题，除了ID主键，重复内容都相同的数据怎么保留第一笔的信息？

[reenjie]

delete from table where 條件 and id not in (select top 1 id from table where 條件)

[littlebirds]

引用 1 楼 reenjie 的回复:

delete from table where 條件 and id not in (select top 1 id from table where 條件)

试过了，不正确。

[zhenshz]

delete from 表 from 表 c,( select 字段,count(*) 次数,min(时间) 时间 from 表 group by 字段,时间 ) as m where c.字段=m.字段 and c.时间>m.时间 and m.次数>1

[霜寒月冷]

------------------------------------------------------------------------------
--Author:霜寒月冷
--VERSION:  Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2) 
--Creation Date:  01/08/14
--Last Modified:  [09:00:32]
----------------------------建表------------------------------------------
if object_id('[tb]') is not null drop table [tb]
go
create table tb (id int,col varchar(10),col2 varchar(20) )
insert into tb
select  1,'111','222'  union all
select  2,'111','222'  union all
select  3,'111','222'  union all
select  4,'111','222'
go

delete from tb where id  in
(
select id from (
select * ,row_number() over  (partition by col,col2 order by id desc) as rn from tb ) t
where t.rn<>1
)
go
select * from tb
----------------------------结果------------------------------------------
/*
id          col        col2
----------- ---------- --------------------
4           111        222
*/

[霜寒月冷]

4#写错了，改了一下

------------------------------------------------------------------------------
--Author:霜寒月冷
--VERSION:  Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2) 
--Creation Date:  01/08/14
--Last Modified:  [09:00:32]
----------------------------建表------------------------------------------
if object_id('[tb]') is not null drop table [tb]
go
create table tb (id int,col varchar(10),col2 varchar(20) )
insert into tb
select  1,'111','222'  union all
select  2,'111','222'  union all
select  3,'111','222'  union all
select  4,'111','222'
go

;with cte as
(
select * ,row_number() over  (partition by col,col2 order by id desc) as rn from tb 

)
delete  from cte where rn<>1
go
select * from tb
----------------------------结果------------------------------------------
/*
id          col        col2
----------- ---------- --------------------
4           111        222
*/