表T_test F_ID F_name F_Area
1 aaa A区
2 bbb B区
3 ccc C区
4 ddd D区
5 eee F区
6 fff A区
7 ggg B区按F_Area来统计每个区的人数(把不属于ABC区的归为其它区内):结果如下
A区 B区 C区 其它区
2 2 1 2
1 aaa A区
2 bbb B区
3 ccc C区
4 ddd D区
5 eee F区
6 fff A区
7 ggg B区按F_Area来统计每个区的人数(把不属于ABC区的归为其它区内):结果如下
A区 B区 C区 其它区
2 2 1 2
insert T_test select 1, 'aaa', 'A区'
union all select 2, 'bbb', 'B区'
union all select 3, 'ccc', 'C区'
union all select 4, 'ddd', 'D区'
union all select 5, 'eee', 'F区'
union all select 6, 'fff', 'A区'
union all select 7, 'ggg', 'B区'select
A区=sum(case when F_Area='A区' then 1 end),
B区=sum(case when F_Area='B区' then 1 end),
C区=sum(case when F_Area='C区' then 1 end),
其它区=sum(case when F_Area not in('A区', 'B区', 'C区') then 1 end)
from T_test--result
A区 B区 C区 其它区
----------- ----------- ----------- -----------
2 2 1 2(1 row(s) affected)
insert into @T_test
select 1 , 'aaa', 'A区' union
select 2 ,'bbb' ,'B区' union
select 3 ,'ccc' ,'C区' union
select 4 ,'ddd' ,'D区' union
select 5 ,'eee' ,'F区' union
select 6 ,'fff' ,'A区' union
select 7 ,'ggg' ,'B区'--按F_Area来统计每个区的人数(把不属于ABC区的归为其它区内):结果如下
-- A区 B区 C区 其它区
-- 2 2 1 2
select A区 = sum(case when F_Area = 'A区' then 1 else 0 end),
B区 = sum(case when F_Area = 'B区' then 1 else 0 end),
C区 = sum(case when F_Area = 'C区' then 1 else 0 end),
其它区 = sum(case when F_Area not in ('A区','B区','C区') then 1 else 0 end)
from @T_test
SUM(CASE area WHEN 'B' THEN num ELSE 0 END) AS B,
SUM(CASE area WHEN 'C' THEN num ELSE 0 END) AS C,
SUM(CASE area WHEN 'A' THEN 0 WHEN 'B' THEN 0 WHEN 'C' THEN 0 ELSE num END)
AS Other
FROM (SELECT CASE f_area WHEN 'A' THEN 'A' WHEN 'B' THEN 'B' WHEN 'C' THEN 'C' ELSE
'other' END AS area, COUNT(f_area) num
FROM f_test
GROUP BY f_area) AS a
SELECT A区 = SUM(CASE WHEN F_Area = 'A区' THEN 1 END),
B区 = SUM(CASE WHEN F_Area = 'B区' THEN 1 END),
C区 = SUM(CASE WHEN F_Area = 'C区' THEN 1 END),
其它区 = SUM(CASE WHEN F_Area NOT IN ('A区', 'B区', 'C区') THEN 1 END)
FROM T_test