數(shù)據(jù)表結(jié)構(gòu)及數(shù)據(jù)：
要統(tǒng)計的報表格式：
SQL語句：
1.只統(tǒng)計最右邊的合計人數(shù)：
select t.addr,
sum( case when(t.type='0') then 1 else 0 end ) as "甲流人數(shù)",
sum( case when(t.type='1') then 1 else 0 end ) as "流感人數(shù)",
sum( case when(t.type='2') then 1 else 0 end ) as "它病人數(shù)",
count(*) as "合計人數(shù)"
from test t
group by t.addr;
2.最右邊和下邊的合計都統(tǒng)計：
(select t.addr as "區(qū)域",
sum( case when(t.type='0') then 1 else 0 end ) as "甲流人數(shù)",
sum( case when(t.type='1') then 1 else 0 end ) as "流感人數(shù)",
sum( case when(t.type='2') then 1 else 0 end ) as "它病戶數(shù)",
count(*) as "合計人數(shù)"
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);
|