select 部门,sum(1) as 员工数,sum(1) as 员工数/(select count() from 表名)*100 as 百分比,sum(case 性别 when m then 1 end) as 男,sum(case 性别 when f then 1 end) as 女 from 表名 group by 部门
凭经验,我觉得是这样的,没有测试过哈。你不妨可以试试。
凭经验,我觉得是这样的,没有测试过哈。你不妨可以试试。
from 员工表
select 部门,count(员工编号) as 员工人数,count(员工编号)/(select count(员工编号) from 员工表) as 百分比 ,sum(case when 性别='m' then 1 else 0 end),sum(case when 性别='f' then 1 else 0 end)
from 员工表 group by 部门
你写的语句与我写的语句有什么区别呢?
select 部门,sum(1) as 员工数,convert(varchar(5,2),sum(1) /(select count() from 表名)*100)+'%' as 百分比,sum(case 性别 when m then 1 end) as 男,sum(case 性别 when f then 1 end) as 女 from 表名 group by 部门
insert into @tableone values('001','a','f')
insert into @tableone values('002','b','m')
insert into @tableone values('003','b','f')
insert into @tableone values('004','a','m')
insert into @tableone values('005','b','m')
insert into @tableone values('006','b','f')
insert into @tableone values('007','a','m')select department,cast(cast(round(personnels*0.1 / totals * 1000,0) as int) as varchar(3))+ '%' as beifenshu,personnels,fman,mman from
(select A.department,(select count(1) from @tableone) as totals,(select count(1) from @tableone where department = A.department) as personnels,
(select count(1) from @tableone where department = A.department and gender='f') as fman,
(select count(1) from @tableone where department = A.department and gender='m') as mman
from @tableone A group by A.department ) B order by B.department--結果
a 43% 3 1 2
b 57% 4 2 2
drop table test
go create table test
(
EmployeeID int,
Section char(1),
sex char(1)
)
goinsert test values(001,'a','f')
insert test values(002,'b','m')
insert test values(003,'b','f')
insert test values(004,'a','m')
insert test values(005,'b','m')
insert test values(006,'b','f')
insert test values(007,'a','m')
goselect * from testselect 部门 = Section,
count(EmployeeID) as 员工数,
rtrim(cast((count(EmployeeID)*100/(select count(EmployeeID) from test)) as char))+'%' as 百分比,
sum( case sex when 'f' then 1 end ) as 男,
sum(case sex when 'm' then 1 end ) as 女
from test group by section