select 部门,count(*) as 总人数, 男=(select count(*) from worker where sex='男' and 部门=a.部门), 女=(select count(*) from worker where sex='女' and 部门=a.部门) from worker A group by 部门
create table worker(part varchar(12),sex int) insert into worker(part,sex) select '2',1 union all select '2',1 union all select '1',1 union all select '1',1 union all select '1',1 union all select '1',1 union all select '1',1 union all select '1',0 union all select '1',1 union all select '1',1 union all select '1',1 union all select '1',0 select part ,count(case when sex=1 then 1 end) as nan ,count(case when sex=0 then 0 end) as nv ,count(*) from worker group by partselect * from worker
select 部门, 总人数 = count(*), 男性人数 = sum(case sex when '男' then 1 else 0 end), 女性人数 = sum(case sex when '女' then 1 else 0 end) from 表 group by 部门
select bumen, grs= count(*), nxrs= sum(select * from biao where sex="M") nxrs= sum(select * from biao where sex="F") group by bumen
Declare @Test Table (部门 varchar(10),性别 varchar(10)) Insert into @Test Select '开发部','男' Union All Select '开发部','男' Union All Select '开发部','女' Union All Select '开发部','男' Union All Select '业务部','女' Union All Select '业务部','女' Union All Select '业务部','男' Union All Select '市场部','男' Union All Select '市场部','女' Union All Select '市场部','男' Union All Select '市场部','女'Select 部门,男生人数=(Select Count(性别) From @Test A Where B.部门=A.部门 and 性别='男'),女生人数=(Select Count(性别) From @Test A Where B.部门=A.部门 and 性别='女') From @Test B Group by 部门(所影响的行数为 11 行)部门 男生人数 女生人数 ---------- ----------- ----------- 开发部 3 1 市场部 2 2 业务部 1 2(所影响的行数为 3 行)
select 部门,总人数=count(*), 男=(select count(*) from worker where sex='男' and 部门=a.部门), 女=(select count(*) from worker where sex='女' and 部门=a.部门) from worker a group by 部门
男=(select count(*) from worker where sex='男' and 部门=a.部门),
女=(select count(*) from worker where sex='女' and 部门=a.部门)
from worker A group by 部门
insert into worker(part,sex)
select '2',1
union all select '2',1
union all select '1',1
union all select '1',1
union all select '1',1
union all select '1',1
union all select '1',1
union all select '1',0
union all select '1',1
union all select '1',1
union all select '1',1
union all select '1',0
select part ,count(case when sex=1 then 1 end) as nan ,count(case when sex=0 then 0 end) as nv ,count(*) from worker group by partselect * from worker
from 表
group by 部门
grs= count(*),
nxrs= sum(select * from biao where sex="M")
nxrs= sum(select * from biao where sex="F")
group by bumen
Insert into @Test Select '开发部','男'
Union All Select '开发部','男'
Union All Select '开发部','女'
Union All Select '开发部','男'
Union All Select '业务部','女'
Union All Select '业务部','女'
Union All Select '业务部','男'
Union All Select '市场部','男'
Union All Select '市场部','女'
Union All Select '市场部','男'
Union All Select '市场部','女'Select 部门,男生人数=(Select Count(性别) From @Test A Where B.部门=A.部门 and 性别='男'),女生人数=(Select Count(性别) From @Test A Where B.部门=A.部门 and 性别='女')
From @Test B Group by 部门(所影响的行数为 11 行)部门 男生人数 女生人数
---------- ----------- -----------
开发部 3 1
市场部 2 2
业务部 1 2(所影响的行数为 3 行)
男=(select count(*) from worker where sex='男' and 部门=a.部门),
女=(select count(*) from worker where sex='女' and 部门=a.部门)
from worker a
group by 部门