Distinct升级---根据几列来筛选唯一数据

 

1.       需求

原始数据如下：

Year	Name	Scroe
2000	A	10
2000	B	8
2000	C	9
2000	D	6
2001	A	8
2001	B	9
2002	A	8
2002	C	7

统计后获取用户最新的成绩：

Year	Name	Scroe
2002	A	8
2001	B	9
2002	C	7
2000	D	6

 

2.       实现

a.       SQL语句实现：

select IDENTITY(INT,1,1) as TID,* into #Tmp from testDistinct order by [name],[year]

 

select * from #Tmp t1 where t1.TID in (select max(T2.TID) from #Tmp t2  group by t2.[name])

 

drop table #Tmp