下面是一个统计的数据库查询,建立了两个临时表,对数据做操作。
还有就是求百分比的操作
- select distinct '行政单位' =
- case
- when 行政区='440608' then '一区'
- when 行政区='440607' then '二区'
- when 行政区='440606' then '三区'
- when 行政区='440605' then '四区'
- when 行政区='440604' then '五区'
- else ''
- end,sum(总金额) as 金额
- into #now
- from 计费
- where 年份=2007 and 起始月份=4 and 结束月份=6
- group by 行政区
- select * from #now
- go
- --drop table #now
- select distinct '行政单位' =
- case
- when 行政区='440608' then '一区'
- when 行政区='440607' then '二区'
- when 行政区='440606' then '三区'
- when 行政区='440605' then '四区'
- when 行政区='440604' then '五区'
- else ''
- end,sum(总金额) as 金额
- into #ago
- from 计费
- where 年份=2007 and 起始月份=1 and 结束月份=3
- group by 行政区
- select * from #ago
- go
- --drop table #ago
- select n.行政单位,n.金额,增长百分比=case
- when isnull(o.金额,0)=0 then '以前没值'
- else
- ltrim(cast(((isnull(n.金额,0)-isnull(o.金额,0))*100/isnull(o.金额,0))AS decimal(9,2)))+'%'
- end
- into #all
- from #now as n
- left outer join #ago as o
- on n.行政单位=o.行政单位
- select * from #all
- //下面是在表里面添加的合计
- select 行政单位,金额,增长百分比,xh=0 from #all
- union
- select 行政单位='合计',金额=sum(q.金额),增长百分比=case
- when isnull(sum(n.金额),0)=0 then '以前没值'
- else
- ltrim(cast(((sum(n.金额)-sum(o.金额))*100/sum(o.金额))AS decimal(9,2)))+'%'
- end,xh=1
- from #all as q,
- #now as n,
- #ago as o
- order by xh
- //删除临时表
- go
- drop table #now
- drop table #ago
- drop table #all