一个数据库查询问题的解决方案

/*
Author:Roboth
Subject:select one item from months[一月取一条的问题]
*/

/*
CSDN的一个问题

现在有表A和表B
表A
userid     username
1            张三
2            李四

表B
userid       userlevel
1            1
1            2
1            3
2            1
2            2

现在要用一条sql查询 或者用视图搞定表搞定表A中userid 在表B中userlevel最高的一条 然后输出userid username和usertoplevel.
userid     username     usertoplevel
1          张三         3
2          李四         2

*/

我的解决方式
--Prepare data
DECLARE @A TABLE(userid int,username varchar(5))
INSERT INTO  @A
select 1,'张三 '
union all
select 2,'李四 '

DECLARE @B TABLE(userid int,userlevel int)
INSERT INTO @B
select 1,1 
union all
select 1,2 
union all
select 1,3 
union all
select 2,1 
union all
select 2,2

--Method1

select
 MAX(A.username),
 B.userid,MAX(B.userlevel) as TopLevel
from @B B
INNER JOIN @A A
ON  A.userid=B.userid
group by B.userid

--Method2

select A.username,B.*
from @B B
INNER JOIN @A A
ON  A.userid=B.userid
where
not exists(select 1 from @B B1 where B1.userlevel>B.userlevel and B1.userid=B.userid)

--Method3

select A.username,B.*
from @B B
INNER JOIN @A A
ON  A.userid=B.userid
where
1 > (select COUNT(1) from @B B1 where B1.userlevel>B.userlevel and B1.userid=B.userid)

--Method4
SELECT A.*,(select MAX(B.userlevel) from @B B where B.userid=A.userid)
from @A A

--Method5
SELECT A.*,(select top 1 (B.userlevel) from @B B where B.userid=A.userid order by B.userlevel DESC)
from @A A

--Method6

select A.*,B.userlevel
from @A A
inner join
(
SELECT userid,MAX(userlevel) as userlevel
from @B
group by userid
) B
on A.userid=B.userid