我有一个数据表DEPARTMENT,用来存储部门人员资料,结构如下:
Department counter
===============================
A 10
B 68
C 45
D 23
E 20
F 10
G 10
H 30
I 90
J 100
现在我想写一条SQL语句,根据每一部门人数,统计该部门人数在所有人数中的百分比,我想输出的结果如下:
Department counter percent
===============================
A 10 5%
B 68 10%
C 45 8%
D 23 15%
E 20 15%
F 10 5%
G 10 4$
H 30 25%
I 90 30%
J 100 40%
------解决方案--------------------
- SQL code
create table tb(Department varchar(10), counter int)insert into tb values('A', 10 )insert into tb values('B', 68 )insert into tb values('C', 45 )insert into tb values('D', 23 )insert into tb values('E', 20 )insert into tb values('F', 10 )insert into tb values('G', 10 )insert into tb values('H', 30 )insert into tb values('I', 90 )insert into tb values('J', 100)goselect t1.* , [percent] = cast(cast((t1.counter*1.0/t2.cnt)*100 as decimal(18,2)) as varchar(5)) + '%' from (select Department , sum(counter) counter from tb group by Department) t1,(select sum(counter) cnt from tb) t2drop table tb/*Department counter percent ---------- ----------- ------- A 10 2.46%B 68 16.75%C 45 11.08%D 23 5.67%E 20 4.93%F 10 2.46%G 10 2.46%H 30 7.39%I 90 22.17%J 100 24.63%（所影响的行数为 10 行）*/
------解决方案--------------------
- SQL code
create table tb(Department varchar(10), counter int)
insert into tb values('A', 10 )
insert into tb values('B', 68 )
insert into tb values('C', 45 )
insert into tb values('D', 23 )
insert into tb values('E', 20 )
insert into tb values('F', 10 )
insert into tb values('G', 10 )
insert into tb values('H', 30 )
insert into tb values('I', 90 )
insert into tb values('J', 100)
go
select Department , sum(counter) counter ,
[percent] = cast(cast((sum(counter)*1.0/(select sum(counter) from tb))*100 as decimal(18,2)) as varchar(5)) + '%'
from tb group by Department
drop table tb
/*
Department counter percent
---------- ----------- -------
A 10 2.46%
B 68 16.75%
C 45 11.08%
D 23 5.67%
E 20 4.93%
F 10 2.46%
G 10 2.46%
H 30 7.39%
I 90 22.17%
J 100 24.63%
（所影响的行数为 10 行）
*/
------解决方案--------------------
create table DEPARTMENT(Department varchar(50),counters int)
insert into DEPARTMENT select 'A',10
insert into DEPARTMENT select 'B',68