有两张表role table:
ID Name
1 aaa
2 bbbuser table:
ID RoleID Name
1 1 ccc
2 1 ddd
3 2 eee求一条SQL返回ID Name UserCount(用户数)
1 aaa 2
2 bbb 1
ID Name
1 aaa
2 bbbuser table:
ID RoleID Name
1 1 ccc
2 1 ddd
3 2 eee求一条SQL返回ID Name UserCount(用户数)
1 aaa 2
2 bbb 1
select 2,'bbb'select 1 id,1 roleid,'ccc' name into [user] union
select 2,1,'ddd' union
select 3,2,'eee'select roleid,role.name,count(9) usercount
from role inner join [user] on role.id=roleid
group by roleid,role.namedrop table role
drop table [user]
where r.id = u.roleid
group by r.id, r.name
select r.id, r.name, count(u.id) from role r, user u
where r.id = u.roleid
group by r.id, r.name
有什么问题吗?