用SQL實(shí)現(xiàn)統(tǒng)計(jì)報(bào)表中的"小計(jì)"與"合計(jì)"的方法詳解

更新時(shí)間：2013年06月11日 09:49:36 作者：

本篇文章是對(duì)使用SQL實(shí)現(xiàn)統(tǒng)計(jì)報(bào)表中的"小計(jì)"與"合計(jì)"的方法進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下

客戶提出需求，針對(duì)某一列分組加上小計(jì)，合計(jì)匯總。網(wǎng)上找了一些有關(guān)SQL加合計(jì)的語句。都不是很理想。決定自己動(dòng)手寫。
思路有三個(gè)：
1.很多用GROUPPING和ROLLUP來實(shí)現(xiàn)。
優(yōu)點(diǎn)：實(shí)現(xiàn)代碼簡潔，要求對(duì)GROUPPING和ROLLUP很深的理解。
缺點(diǎn)：低版本的Sql Server不支持。

2.游標(biāo)實(shí)現(xiàn)。
優(yōu)點(diǎn)：思路邏輯簡潔。
缺點(diǎn)：復(fù)雜和低效。

3.利用臨時(shí)表。
優(yōu)點(diǎn)：思路邏輯簡潔，執(zhí)行效率高。SQL實(shí)現(xiàn)簡單。
缺點(diǎn)：數(shù)據(jù)量大時(shí)耗用內(nèi)存.

綜合三種情況，決定“利用臨時(shí)表”實(shí)現(xiàn)。
實(shí)現(xiàn)效果
原始表TB

加上小計(jì)，合計(jì)后效果

SQL語句

復(fù)制代碼代碼如下:

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,'小計(jì)' 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,'合計(jì)' 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 