数据表结构及数据：
要统计的报表格式：
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);
|