數據表結構及數據：
要統計的報表格式：
SQL語句：
1.只統計最右邊的合計人數：
select t.addr,
sum( case when(t.type='0') then 1 else 0 end ) as "甲流人數",
sum( case when(t.type='1') then 1 else 0 end ) as "流感人數",
sum( case when(t.type='2') then 1 else 0 end ) as "它病人數",
count(*) as "合計人數"
from test t
group by t.addr;
2.最右邊和下邊的合計都統計：
(select t.addr as "區域",
sum( case when(t.type='0') then 1 else 0 end ) as "甲流人數",
sum( case when(t.type='1') then 1 else 0 end ) as "流感人數",
sum( case when(t.type='2') then 1 else 0 end ) as "它病戶數",
count(*) as "合計人數"
from test t
group by t.addr)
union
(select null, sum( case when(t.type='0') then 1 else 0 end ),
sum( case when(t.type='1') then 1 else 0 end ),
sum( case when(t.type='2') then 1 else 0 end ),
count(*)
from test t);
|