DECLARE @a table(dept_id int, emp_id int, sex varchar(2), marry varchar(2))
INSERT @a SELECT 1, 1 ,'M', 'Y'
UNION ALL SELECT 1, 2 ,'F' ,'N'
UNION ALL SELECT 1, 3 ,'F' ,'Y'
UNION ALL SELECT 1, 4 ,'F' ,'N'
UNION ALL SELECT 1, 5 ,'M' ,'N'
UNION ALL SELECT 2, 6 ,'F' ,'Y'
UNION ALL SELECT 2, 7 ,'M' ,'N'
UNION ALL SELECT 2, 8 ,'M' ,'N' SELECT dept_id,
[人数(男/女)]=ltrim(count(1))+'('+ltrim(sum(case when sex='M' THEN 1 else 0 END))
+'/'+ltrim(sum(case when sex='F' THEN 1 ELSE 0 end)) +')',
[已婚/未婚]=ltrim(sum(case when marry='Y' then 1 else 0 end))+'/' +
ltrim(sum(CASE WHEN marry='N' THEN 1 ELSE 0 end))
FROM @a
GROUP BY dept_id
--result
/*dept_id 人数(男/女) 已婚/未婚
----------- --------------------------------------- -------------------------
1 5(2/3) 2/3
2 3(2/1) 1/2(所影响的行数为 2 行)*/
INSERT @a SELECT 1, 1 ,'M', 'Y'
UNION ALL SELECT 1, 2 ,'F' ,'N'
UNION ALL SELECT 1, 3 ,'F' ,'Y'
UNION ALL SELECT 1, 4 ,'F' ,'N'
UNION ALL SELECT 1, 5 ,'M' ,'N'
UNION ALL SELECT 2, 6 ,'F' ,'Y'
UNION ALL SELECT 2, 7 ,'M' ,'N'
UNION ALL SELECT 2, 8 ,'M' ,'N' SELECT dept_id,
[人数(男/女)]=ltrim(count(1))+'('+ltrim(sum(case when sex='M' THEN 1 else 0 END))
+'/'+ltrim(sum(case when sex='F' THEN 1 ELSE 0 end)) +')',
[已婚/未婚]=ltrim(sum(case when marry='Y' then 1 else 0 end))+'/' +
ltrim(sum(CASE WHEN marry='N' THEN 1 ELSE 0 end))
FROM @a
GROUP BY dept_id
--result
/*dept_id 人数(男/女) 已婚/未婚
----------- --------------------------------------- -------------------------
1 5(2/3) 2/3
2 3(2/1) 1/2(所影响的行数为 2 行)*/
--> 测试数据: @s
declare @s table (dept_id int,emp_id int,sex varchar(1),marry varchar(1))
insert into @s
select 1,1,'M','Y' union all
select 1,2,'F','N' union all
select 1,3,'F','Y' union all
select 1,4,'F','N' union all
select 1,5,'M','N' union all
select 2,6,'F','Y' union all
select 2,7,'M','N' union all
select 2,8,'M','N'
select dept_id,
[人数(男/女)]=ltrim(count(1))+'('+ltrim(sum(case sex when 'M' then 1 else 0 end))+'/'+ltrim(sum(case sex when 'F' then 1 else 0 end))+')',
[已婚/未婚]=ltrim(sum(case marry when 'Y' then 1 else 0 end))+'/'+ltrim(sum(case marry when 'N' then 1 else 0 end))
from @s
group by dept_id
declare @t table(dept_id int, emp_id int, sex varchar(2), marry varchar(2))
insert @t select 1, 1, 'M', 'Y'
insert @t select 1, 2, 'F' ,'N'
insert @t select 1, 3, 'F' ,'Y'
insert @t select 1, 4, 'F' ,'N'
insert @t select 1, 5, 'M', 'N'
insert @t select 2, 6, 'F' ,'Y'
insert @t select 2, 7, 'M', 'N'
insert @t select 2, 8, 'M', 'N'
select dept_id , count(*)總人數,
rtrim(cast(sum(case sex when 'M' then 1 else 0 end)*100.0/count(*) as int))+'%' 男比例,
rtrim(cast(sum(case sex when 'F' then 1 else 0 end)*100.0/count(*) as int))+'%' 女比例,
rtrim(cast(sum(case marry when 'Y' then 1 else 0 end)*100.0/count(*) as int))+'%'已婚比例
from
@t
group by
dept_id
/*
dept_id 總人數 男比例 女比例 已婚比例
----------- ----------- ------------- ------------- -------------
1 5 40% 60% 40%
2 3 66% 33% 33%
*/
declare @tb table(dept_id int, emp_id int, sex varchar(10), marry varchar(10))
insert @tb
select 1, 1, 'M', 'Y' union all
select 1, 2 ,'F' ,'N' union all
select 1, 3 ,'F' ,'Y' union all
select 1, 4 ,'F' ,'N' union all
select 1, 5 ,'M', 'N' union all
select 2, 6 ,'F' ,'Y' union all
select 2, 7 ,'M' ,'N' union all
select 2, 8, 'M' ,'N' select dept_id as [部门],rtrim(num)+'('+rtrim(Male)+'/'+rtrim(Female)+')' as [人数(男/女)],rtrim(Married)+'/'+rtrim(Unmarried) as [已婚/未婚 ]
from (
select dept_id,count(*) as num,sum(case when sex='M' then 1 else 0 end) as Male,sum(case when sex='F' then 1 else 0 end) as Female,
sum(case when marry='Y' then 1 else 0 end) as Married,sum(case when marry='N' then 1 else 0 end) as Unmarried
from @tb
group by dept_id
) a
/*
部门 人数(男/女) 已婚/未婚
----------- --------------------------------------- -------------------------
1 5(2/3) 2/3
2 3(2/1) 1/2(2 row(s) affected)
*/
INSERT @a SELECT 1, 1 ,'M', 'Y'
UNION ALL SELECT 1, 2 ,'F' ,'N'
UNION ALL SELECT 1, 3 ,'F' ,'Y'
UNION ALL SELECT 1, 4 ,'F' ,'N'
UNION ALL SELECT 1, 5 ,'M' ,'N'
UNION ALL SELECT 2, 6 ,'F' ,'Y'
UNION ALL SELECT 2, 7 ,'M' ,'N'
UNION ALL SELECT 2, 8 ,'M' ,'N' select dept_id,人数=(cast(count(*)as varchar(10))+'('+cast((select count(*)from @a where dept_id = b.dept_id and sex = 'M')as varchar(10))
+'/'+cast((select count(*)from @a where dept_id = b.dept_id and sex = 'F')as varchar(10))+')'),
'已婚/未婚' =(cast((select count(*)from @a where dept_id = b.dept_id and marry = 'Y')as varchar(10))+'/'+cast((select count(*)from @a where dept_id = b.dept_id and marry = 'N')as varchar(10)) )
from @a b group by dept_id