求返回 字段为 count(workid) username 的表,按顺序排列!!
谢谢!!!!
works:
-------------
workid userid
2 1
1 1
5 2users:
------------
userid username
1 yezi
2 robin
谢谢!!!!
works:
-------------
workid userid
2 1
1 1
5 2users:
------------
userid username
1 yezi
2 robin
u.username,count(w.userid)
from
users u
left join
works w
on
u.userid=w.userid
group by
u.userid,u.username
order by
u.userid
group by a.userid order by a.userid
left join (select userid,workid = count(workid) from works group by userid )b
on a.userid = b.userid
create table #m2(userid int,username char(20) )insert into #m1
select 2,1 union all
select 1,1 union all
select 5,2 insert into #m2
select 1,'yezi' union all
select 2,'robin' select * from #m1select * from #m2select #m2.username,count(#m1.workid)次数
from #m2
left join #m1
on #m1.userid=#m2.userid
group by #m2.username
order by #m2.username
drop table #m1
drop table #m2