select a.departmentId,a.departmentName,count(*) 用户个数
from table_a a left join table_b b on a.departmentId=b.departmentId
group by a.departmentId,a.departmentName
from table_a a left join table_b b on a.departmentId=b.departmentId
group by a.departmentId,a.departmentName
from table_a a left join table_b b
on a.departmentId=b.departmentId
group by a.departmentId,a.departmentName
select departmentname as 部门,
count(*) as 用户个数
from table_a a left join table_b b on a.departmentid=b.departmentid
gropu by departmentname
count(b.*) 用户个数
from table_a a left join table_b b
on a.departmentId=b.departmentId
group by a.departmentName
count(b.*) 用户个数
from table_a a left join table_b b
on a.departmentId=b.departmentId
group by a.departmentName
departmentId int ,departmentName varchar(30))declare @table_b table (
userId int ,departmentId int ,userName varchar(30))
insert into @table_a select 1,'aa' union select 2,'bb' union select 3,'cc'insert into @table_b select 1,1,'liguanggge' union select 2,2,'wangy'select a.departmentName,
(select count(*) from @table_b where departmentId=a.departmentId) as 用户个数
from @table_a a
--测试结果
departmentName 用户个数
------------------------------ -----------
aa 1
bb 1
cc 0(3 row(s) affected)
select a.departmentId,a.departmentName,count(b.departmentId) 用户个数
from table_a a left join table_b b on a.departmentId=b.departmentId
group by a.departmentId,a.departmentName