用grouping求小计和合计
来源:互联网 发布:网络物流信息平台 编辑:程序博客网 时间:2024/05/17 03:02
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)select case when grouping(a)=1 then '合计' else cast(a as varchar) end a, b, sum(c) as c,sum(d) as d,sum(e) as efrom #t group by a,b with rollup having grouping(b)=0 or grouping(a)=1
结果:
a b c d e
------------------------------ ----------- ----------- ----------- -----------
1 2 12 16 26
1 3 12 16 26
2 2 14 16 26
2 3 15 16 26
合计 NULL 53 64 104
(所影响的行数为 5 行)
select case when grouping(a)=1 then '合计' else cast(a as varchar) end a, b, c, sum(d) as d,sum(e) as efrom #t group by a,b,c with rollup having grouping(c)=0 or grouping(a)=1
结果:
a b c d e
------------------------------ ----------- ----------- ----------- -----------
1 2 3 16 26
1 3 3 16 26
2 2 2 4 5
2 2 3 4 6
2 2 4 4 7
2 2 5 4 8
2 3 3 12 21
2 3 6 4 5
合计 NULL NULL 64 104
(所影响的行数为 9 行)
select case when grouping(a)=1 then '合计' else cast(a as varchar) end a, b, case when grouping(c)=1 and grouping(b)=0 then '小计' else cast(c as varchar) end c, sum(d) as d,sum(e) as efrom #t group by a,b,c with rollup having grouping(a)=1 or grouping(b)=0
结果:
a b c d e
------------------------------ ----------- ------------------------------ ----------- -----------
1 2 3 16 26
1 2 小计 16 26
1 3 3 16 26
1 3 小计 16 26
2 2 2 4 5
2 2 3 4 6
2 2 4 4 7
2 2 5 4 8
2 2 小计 16 26
2 3 3 12 21
2 3 6 4 5
2 3 小计 16 26
合计 NULL NULL 64 104
(所影响的行数为 13 行)
select case when grouping(a)=1 then '合计' else cast(a as varchar) end a, case when grouping(b)=1 and grouping(a)=0 then '小计' else cast(b as varchar) end b, sum(c) as c, sum(d) as d,sum(e) as e from #t group by a,b,c with rollup having grouping(a)=1 or grouping(b)=1 or grouping(c)=0
结果:
a b c d e
------------------------------ ------------------------------ ----------- ----------- -----------
1 2 12 16 26
1 3 12 16 26
1 小计 24 32 52
2 2 2 4 5
2 2 3 4 6
2 2 4 4 7
2 2 5 4 8
2 3 9 12 21
2 3 6 4 5
2 小计 29 32 52
合计 NULL 53 64 104
(所影响的行数为 11 行)
drop table #t
- 用grouping求小计和合计
- 用sql写小计和合计
- 用SQL进行报表的小计和合计
- 用SQL进行报表的小计和合计
- oracle 小计合计之 group by grouping sets
- Oracle分组小计、总计示例(grouping sets的使用)
- Oracle分组小计、总计示例(grouping sets的使用)
- ms sql 行转列 实现表中统计与小计的
- zoj3795 Grouping --- 强连通,求最长路
- GROUPING
- grouping
- Grouping
- Grouping
- GROUPING
- GROUPING
- GridView加入自动求和求平均值小计
- GridView加入自动求和求平均值小计
- GridView加入自动求和求平均值小计
- 我勒个去,苹果你故意的吧?又丢手机了?
- Change Rows Background color in GridView with selected Criteria
- Nginx反向代理到apache
- 项目经理的职责和权利
- ubuntu下mysql安装(server、client、dev),开启、停止和重启,及常见错误
- 用grouping求小计和合计
- linux 备份系统方法
- Java拼接多张图片,可以连接在一起
- paradox数据库的创建与数据操作
- java 内部类
- Tcode
- Check/Uncheck All Checkboxes in Asp.Net Gridview
- asp:FileUpload 上传文件
- volatile 和 sig_atomic_t