请教一条SQL语句，急急急，请高手指点

[jkjj20]

数据表

keyId typeId userName addYear addMonth
1 0001 小张 2010 9
2 0001 小李 2011 8
3 0002 小王 2008 7
4 0002 小赵 2006 6
5 0003 小刘 2005 4

要求取得每个分类里面注册时间最晚的记录，也就是说取得每个分类里面，年份最近，月份最近的记录集，希望的查询结果是
keyId typeId userName addYear addMonth

2 0001 小李 2011 8
3 0002 小王 2008 7
5 0003 小刘 2005 4

[hllfl]

select keyId,typeId,userName,addYear,addMonth
from tb 
where addYear in(select max(addYear) from tb);

[hllfl]

select keyId,typeId,userName,addYear,addMonth
from tb 
where addYear in(select typeId,max(addYear) from tb group by typeId);

[pengxuan]

if object_id('tb','U') is not null
   drop table tb
go
create table tb
(
 keyId int identity(1,1),
 typeId varchar(10),
 userName varchar(10),
 addYear int,
 addMonth int
)
go
insert into tb (typeId,userName,addYear,addMonth)
select '0001','小张',2010,9 union all
select '0001','小李',2011,8 union all
select '0002','小王',2008,7 union all
select '0002','小赵',2006,6 union all
select '0003','小刘',2005,4
go
select * from tb a where not exists(select 1 from tb where typeId=a.typeId and cast(cast(addYear as varchar)+'-'+cast(addMonth as varchar)+'-01' as datetime)>cast(cast(a.addYear as varchar)+'-'+cast(a.addMonth as varchar)+'-01' as datetime))
go
/*
keyId       typeId     userName   addYear     addMonth
----------- ---------- ---------- ----------- -----------
2           0001       小李         2011        8
3           0002       小王         2008        7
5           0003       小刘         2005        4

(3 行受影响)


*/

[jkjj20]

这样不对哦，对于一个分类我只取一条记录，如果我的数据里面有多条同年份的记录你这个就搞不定了

[中国风]

--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#T') is null
	drop table #T
Go
Create table #T([keyId] int,[typeId] nvarchar(4),[userName] nvarchar(2),[addYear] int,[addMonth] int)
Insert #T
select 1,N'0001',N'小张','2010',9 union all
select 2,N'0001',N'小李','2011',8 union all
select 3,N'0002',N'小王','2008',7 union all
select 4,N'0002',N'小赵','2006',6 union all
select 5,N'0003',N'小刘','2005',4
Go
Select * from #T as a 
where not exists(select 1 from #T where  [typeId]=a.[typeId] and [addYear]>a.[addYear] or([addYear]=a.[addYear] and [addMonth]>a.[addMonth]))

/*
keyId	typeId	userName	addYear	addMonth
2	0001	小李	2011	8
3	0002	小王	2008	7
5	0003	小刘	2005	4
*/

[AcHerat]

select *
from tb t
where not exists (select 1 from tb where typeid = t.typeid and (addyear>t.addyear or (addyear = t.addyear and addmonth > t.addmonth))

[中国风]

取keyId大小时

--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#T') is null
	drop table #T
Go
Create table #T([keyId] int,[typeId] nvarchar(4),[userName] nvarchar(2),[addYear] int,[addMonth] int)
Insert #T
select 1,N'0001',N'小张','2010',9 union all
select 2,N'0001',N'小李','2011',8 union all
select 3,N'0002',N'小王','2008',7 union all
select 4,N'0002',N'小赵','2006',6 union all
select 5,N'0003',N'小刘','2005',4
Go
Select * from #T as a 
where not exists(select 1 from #T where  [typeId]=a.[typeId] and [keyId]>a.[keyId])

/*
keyId	typeId	userName	addYear	addMonth
2	0001	小李	2011	8
4	0002	小赵	2006	6
5	0003	小刘	2005	4
*/

[AcHerat]

6楼最后少个括号，大版你真快啊！纠结死这里的网速。。。

[中国风]

其它方法参照
http://topic.csdn.net/u/20080626/00/43d0d10c-28f1-418d-a05b-663880da278a.html

[ww221216]

select keyId,typeId,userName,addYear,addMonth
from tb
where addYear in(select typeId,max(addYear) from tb group by typeId);

[中国风]

[Quote=引用 8 楼 acherat 的回复:]

6楼最后少个括号，大版你真快啊！纠结死这里的网速。。。
[/Quote]

[ww221216]

select keyId,typeId,userName,addYear,addMonth
from tb
where addYear in(select typeId,max(addYear) from tb group by typeId);

[中国风]

再贴一方法

SQL05以上版本可这样用

select *
from (select *,row_number()over(partition by [typeId] order by [addYear] desc,[addMonth] desc) as row from #T a)T 
where row=1

[苦苦的潜行者]

/*
(苦苦的潜行者)
*/
if object_id('t1') is not null
drop table t1
go
create table t1(keyid int,typeid varchar(10),username varchar(10),addyear varchar(10),addmonth varchar(10))
go
insert t1
select 1,'0001','小张','2010','9' union all
select 2,'0001','小李','2011','8' union all
select 3,'0002','小王','2008','7' union all
select 4,'0002','小赵','2006','6' union all
select 5,'0005','小刘','2005','4'
go
select * from t1 a where
not exists (select 1 from t1 
where a.typeid=typeid
and convert(varchar(5),a.addyear+'-'+a.addmonth,120)<convert(varchar(5),addyear+'-'+addmonth,120)
)
/*
（所影响的行数为 3 行）
keyid  typeid username addyear addmonth
----    ----    ------  -----  ----
2	0001	小李	2011	8
3	0002	小王	2008	7
5	0005	小刘	2005	4
*/
go
drop table t1

[苦苦的潜行者]

[Quote=引用 14 楼 xiaolinyouni 的回复:]

SQL code
/*
(苦苦的潜行者)
*/
if object_id('t1') is not null
drop table t1
go
create table t1(keyid int,typeid varchar(10),username varchar(10),addyear varchar(10),addmonth varchar(10))
go
insert t1
selec……
[/Quote]
将convert(varchar(5)..)换成convert(varchar(6)..)不然当月份格式为双数时会出错.