用SQL实现统计报表中的"小计"与"合计"的方法详解
沈晓海老婆,便携式高倍放大镜,极速翱翔
客户提出需求,针对某一列分组加上小计,合计汇总。网上找了一些有关sql加合计的语句。都不是很理想。决定自己动手写。
思路有三个:
1.很多用groupping和rollup来实现。
优点:实现代码简洁,要求对groupping和rollup很深的理解。
缺点:低版本的sql server不支持。
2.游标实现。
优点:思路逻辑简洁。
缺点:复杂和低效。
3.利用临时表。
优点:思路逻辑简洁,执行效率高。sql实现简单。
缺点:数据量大时耗用内存.
综合三种情况,决定“利用临时表”实现。
实现效果
原始表tb
加上小计,合计后效果
sql语句
select * into #tb from tb
select * into #tb1 from #tb where 1<>1
select distinct zcxt into #tbype from #tb order by zcxt
select identity(int,1,1) fid,zcxt into #tbype1 from #tbype
declare @i int
declare @k int
select @i=count(*) from #tbype
set @k=0
declare @strfname varchar(50)
while @k < @i
begin
set @k =@k +1
select @strfname=zcxt from #tbype1 where fid =@k
set identity_insert #tb1 on
insert into #tb1(fid,qldid,fa_cardid,ztbz,fa_name,model,i_number,gzrq,zcyz,ljzj,jz,sybm,zcxt,fa_ljjzzb)
select fid,qldid,fa_cardid,ztbz,fa_name,model,i_number,gzrq,zcyz,ljzj,jz,sybm,zcxt,fa_ljjzzb from
(
select * from #tb where zcxt=@strfname
union all
select 0 fid,'' qldid,'' fa_cardid,'' ztbz,'小计' fa_name,'' model,sum(i_number) as i_number,'' gzrq,sum(cast(zcyz as money)) as zcyz,sum(cast(ljzj as money)) as ljzj,sum(cast(jz as money)) as jz,'' sybm,'' zcxt,sum(fa_ljjzzb) as fa_ljjzzb
from #tb where zcxt=@strfname
group by ztbz
) as b
set identity_insert #tb1 off
end
select qldid,fa_cardid,zcxt,fa_name,model,i_number,gzrq,zcyz,ljzj,jz,sybm,ztbz,fa_ljjzzb from #tb1
union all
select '' qldid,'' fa_cardid,'' ztbz,'合计' fa_name,'' model,sum(i_number) as i_number,'' gzrq,sum(cast(zcyz as money)) as zcyz,sum(cast(ljzj as money)) as ljzj,sum(cast(jz as money)) as jz,'' sybm,'' zcxt,sum(fa_ljjzzb) as fa_ljjzzb
from #tb
drop table #tb1
drop table #tbype1
drop table #tbype
drop table #tb
扩展改进
可以改写成一个通用的添加合计小计的存储过程。
如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!!
点击进行留言回复
相关文章:
-
-
-
-
-
-
-
-
-
-
-
mysql事务管理操作详解
本文实例讲述了mysql事务管理操作。分享给大家供大家参考,具体如下:本文内容: 什么是事务管理 事务管理操作 回滚点 默认的事务管理首发日期:2018...
[阅读全文]
网友评论