查询每个部门的人员名字,和人员总数?(在线等)
查询每个部门的人员名字,和人员总数.
create table a(id int,name varchar(20),depname varchar(20))
name为人员名字.
depname 为部门名字.
[解决办法]
create table a(id int,name varchar(20),depname varchar(20))
insert into a
select 1, 'test1 ', 'it ' union all
select 2, 'test2 ', 'it ' union all
select 3, 'test3 ', 'accounting '
select a.depname,name,b.num from a inner join
(
select depname,count(1) num from a group by depname) b
on a.depname=b.depname
drop table a
[解决办法]
查询每个部门的人员名字,和人员总数.
create table a(id int,name varchar(20),depname varchar(20))
name为人员名字.
depname 为部门名字.
select depname as 部门名字, count(*) as 人员总数 from a group by depname
[解决办法]
我测试过了，你可以用：
select a1.depname,a1.[name],(select count(*)
from a a2
where a1.depname = a2.depname
group by depname) 员工人数
from a a1
order by a1.depname
[解决办法]
select name,depname,countname=(select count(*) from a where depname=b.depname group by depname) from a b
