用SQL完成統計報表中的"小計"與"算計"的辦法詳解。本站提示廣大學習愛好者：（用SQL完成統計報表中的"小計"與"算計"的辦法詳解）文章只能為提供參考，不一定能成為您想要的結果。以下是用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
擴大改良
可以改寫成一個通用的添加算計小計的存儲進程。
