select sf,diqu,eee,rrr,tttfrom test
union all
select '',diqu+'合计' as dqu,sum(eee),sum(rrr),sum(ttt) from test group by diqu
union all
select '', '总计'+sf as sf,sum(eee),sum(rrr),sum(ttt) from test group by sf
order by diqu
union all
select '',diqu+'合计' as dqu,sum(eee),sum(rrr),sum(ttt) from test group by diqu
union all
select '', '总计'+sf as sf,sum(eee),sum(rrr),sum(ttt) from test group by sf
order by diqu
declare @a table(省份 char(10),地区 char(10),eee int,rrr int,ttt int)
insert into @a select '陕西','西安',1,2,3 union all
select '陕西','西安',1,2,3 union all
select '陕西','西安',1,2,3 union all
select '陕西','榆林',1,2,3 union all
select '陕西','榆林',1,2,3 union all
select '陕西','榆林',1,2,3 union all
select '陕西','榆林',1,2,3
select * from @a where 地区='西安'
union all select 省份='西安合计',地区='',sum(eee) as eee,sum(rrr) as rrr ,sum(ttt) as ttt from @a where 地区='西安'
union all select * from @a where 地区='榆林'
union all select 省份='榆林合计',地区='',sum(eee) as eee ,sum(rrr) as rrr,sum(ttt) as ttt from @a where 地区='榆林'
union all select 省份='陕西合计',地区='',sum(eee) as eee ,sum(rrr) as rrr,sum(ttt) as ttt from @a
省份 地区 eee rrr ttt
---------- ---------- ----------- ----------- -----------
陕西 西安 1 2 3
陕西 西安 1 2 3
陕西 西安 1 2 3
西安合计 3 6 9
陕西 榆林 1 2 3
陕西 榆林 1 2 3
陕西 榆林 1 2 3
陕西 榆林 1 2 3
榆林合计 4 8 12
陕西合计 7 14 21(所影响的行数为 10 行)