根据多个（非一个）字段删除重复记录，数据如下
ID Dep UserID UserName sDate
6DD260E6-3A6B-42ED-8630-197802AAB6B3财务部_f_00017刘小宝 2012-08-27 00:00:00.000
FB61B5B3-8E8A-4852-87AC-AFBA081FD858财务部_f_00017刘小宝 2012-08-27 00:00:00.000
D839A562-0198-418E-B683-239C49871E90财务部_f_00016王明 2012-08-27 00:00:00.000
7B1DCCBC-B31E-4858-8DFF-763F80DE0198财务部_f_00016王明 2012-08-27 00:00:00.000
6821CCE4-C7A6-4971-8FA6-AA31CD0D327A产供部_f_00027张彬彬 2012-08-27 00:00:00.000
256B1839-A6B9-4756-9B8A-0267C3F7688E产供部_f_00027张彬彬 2012-08-27 00:00:00.000
要求：根据 UserID,sDate 删除重复记录，重复记录只保留一条
期望结果如下：
ID Dep UserID UserName sDate
6DD260E6-3A6B-42ED-8630-197802AAB6B3财务部_f_00017刘小宝 2012-08-27 00:00:00.000
D839A562-0198-418E-B683-239C49871E90财务部_f_00016王明 2012-08-27 00:00:00.000
6821CCE4-C7A6-4971-8FA6-AA31CD0D327A产供部_f_00027张彬彬 2012-08-27 00:00:00.000
[解决办法]
delete tb where not exists
(select 1 from tb as a where a.userid=tb.userid and a.sDate=tb.sDate
group by a.userid,a.sDate having min(a.id)=tb.id)
[解决办法]
with tb as
(
SELECT tbtmp.*,row=ROW_NUMBER()OVER(Partition by UserID, sDate order by ID) FROM yourtable
)
delete from yourtable
where ID in (
SELECT ID from tb
where row <>1 )
[解决办法]
用到uniqueidentifier 列的时候转换一下 转成nvarchar
例如
delete tb where not exists
(select 1 from tb as a where a.userid=tb.userid and a.sDate=tb.sDate
group by a.userid,a.sDate having min(cast(a.id as varchar(40)))=cast(tb.id as varchar(40)))
delete a
from （select * ,row_number()over( partition by UserID,sDate order by ID) as Row from 表名) as a
where Row>1
