建站
咨詢
售后
建站咨詢
新聞中心
以下的文章主要描述的是SQL Server Rollup關(guān)鍵字的具體使用技巧，在其實(shí)際操作中我們首先是創(chuàng)建測試表、添加數(shù)據(jù)，以下就是文章的主要內(nèi)容的詳細(xì)描述，望大家在瀏覽之后會(huì)對(duì)其有更深的了解。
創(chuàng)新互聯(lián)建站是專業(yè)的金口河網(wǎng)站建設(shè)公司，金口河接單;提供成都網(wǎng)站制作、網(wǎng)站設(shè)計(jì)、外貿(mào)網(wǎng)站建設(shè),網(wǎng)頁設(shè)計(jì),網(wǎng)站設(shè)計(jì),建網(wǎng)站,PHP網(wǎng)站建設(shè)等專業(yè)做網(wǎng)站服務(wù);采用PHP框架,可快速的進(jìn)行金口河網(wǎng)站開發(fā)網(wǎng)頁制作和功能擴(kuò)展;專業(yè)做搜索引擎喜愛的網(wǎng)站,專業(yè)的做網(wǎng)站團(tuán)隊(duì),希望更多企業(yè)前來合作!
- create table #t(a int,b int,c int,d int,e int)
- insert into #t values(1,2,3,4,5)
- insert into #t values(1,2,3,4,6)
- insert into #t values(1,2,3,4,7)
- insert into #t values(1,2,3,4,8)
- insert into #t values(1,3,3,4,5)
- insert into #t values(1,3,3,4,6)
- insert into #t values(1,3,3,4,8)
- insert into #t values(1,3,3,4,7)
- insert into #t values(2,2,2,4,5)
- insert into #t values(2,2,3,4,6)
- insert into #t values(2,2,4,4,7)
- insert into #t values(2,2,5,4,8)
- insert into #t values(2,3,6,4,5)
- insert into #t values(2,3,3,4,6)
- insert into #t values(2,3,3,4,8)
- insert into #t values(2,3,3,4,7)
情況一：只有一個(gè)分類匯總列時(shí)，只需要一個(gè)合計(jì)。只需要增加with rollup即可。
- select case when grouping(a)=1 then '合計(jì)' else cast(a as varchar) end a,
- sum(b),sum(c),sum(d),sum(e) from #t group by a with rollup
情況二:有多個(gè)分類匯總列，只需要一個(gè)合計(jì).增加SQL Server rollup之后，需要增加判斷。
- select case when grouping(a)=1 then '合計(jì)' else cast(a as varchar) end a,
- b,
- sum(c),sum(d),sum(e) from #t
- group by a,b with rollup
- having grouping(b)=0 or grouping(a)=1select case when grouping(a)=1 then '合計(jì)' else cast(a as varchar) end a,
- b,
- c,
- sum(d),sum(e) from #t
- group by a,b,c with rollup
- having grouping(c)=0 or grouping(a)=1
情況三：有多個(gè)分類匯總列，需要全部的小計(jì)和合計(jì)。
- select case when grouping(a)=1 then '合計(jì)' else cast(a as varchar) end a,
- case when grouping(b)=1 and grouping(a)=0 then '小計(jì)' else cast(b as varchar) end b,
- case when grouping(c)=1 and grouping(b)=0 then '小計(jì)' else cast(c as varchar) end c,
- sum(d),sum(e) from #t
- group by a,b,c with rollup
另外一種顯示小計(jì)的方式
- select case when grouping(a)=1 then '合計(jì)'
- when grouping(b)=1 then cast(a as varchar)+'小計(jì)'
- else cast(a as varchar) end a,
- case when grouping(b)=0 and grouping(c)=1
- then cast(b as varchar)+'小計(jì)' else cast(b as varchar) end b,
- case when grouping(c)=1 and grouping(b)=0
- then '' else cast(c as varchar) end c,
- sum(d),sum(e) from #t
- group by a,b,c with rollup
情況四：有多個(gè)分類匯總列，需要部分的小計(jì)和合計(jì)
- select case when grouping(a)=1 then '合計(jì)' else cast(a as varchar) end a,
- b,
- case when grouping(c)=1 and grouping(b)=0 then '小計(jì)' else cast(c as varchar) end c,
- sum(d),sum(e) from #t
- group by a,b,c with rollup
- having grouping(a)=1 or grouping(b)=0
- select case when grouping(a)=1 then '合計(jì)' else cast(a as varchar) end a,
- case when grouping(b)=1 and grouping(a)=0 then '小計(jì)' else cast(b as varchar) end b,
- c,
- sum(d),sum(e) from #t
- group by a,b,c with rollup
- having grouping(a)=1 or grouping(b)=1 or grouping(c)=0
上述的相關(guān)內(nèi)容就是對(duì)SQL Server Rollup關(guān)鍵字使用技巧的描述，希望會(huì)給你帶來一些幫助在此方面。
網(wǎng)站標(biāo)題：SQLServerRollup關(guān)鍵字的使用竅門
標(biāo)題URL：http://www.dlmjj.cn/article/cdidoeg.html
