有两张表
role table:
ID Name
1 aaa
2 bbb
user table:
ID RoleID Name
1 1 ccc
2 1 ddd
3 2 eee
求一条SQL返回
ID Name UserCount(用户数)
1 aaa 2
2 bbb 1
------解决方案--------------------
select r.id, r.name, count(u.id) from role r, user u
where r.id = u.roleid
group by r.id, r.name
------解决方案--------------------
select ID,name,UserCount=(select count(*) from [user] where roleID = role.ID) from role