ID---用户---天数----日期---
A---1001-----1-----2015-11-1
B---1001-----2-----2015-11-5
C---1001-----3-----2015-11-6
D---1002-----1-----2015-11-2
E---1002-----2-----2015-11-8
F---1003-----1-----2015-10-1
请教:ms-sqlserver如何只保留每个用户“天数”最大的那条记录,用一条sql把ID为A,B,D的数据删除?
delete t FROM table1 AS t
INNER JOIN (
SELECT *,ROW_NUMBER()OVER(PARTITION BY 用户 order by 天数 desc) seq FROM table1
) tt.ID=t.ID AND tt.seq!=1
delete from table1 where id not in
(
select id from table1 a ,
(
select 用户,MAX(天数) as 天数 from table1 group by 用户
) b
where a.用户=b.用户 and a.天数=b.天数
)