一个数据库查询的问题...
现在有表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
[解决办法]
select a.userid,a.username,nz(b1.ma,0)
from a left join
(select userid,max(usertoplevel) as ma from b group by userid) b1
on a.userid=b1.userid
[解决办法]
select
A.userid,
A.username,
nz(T.FF,0) AS usertoplevel
from A
left join (select userid, max(userlevel) as FF from b group by userid) T on A.userid=T.userid
