怎样在sqlserver2005中实现小计、合计
用存储过程或其他方式实现小计和合计的报表统计,提供思路也行，前台展示我用的是GridView，在后台代码或控件中操作也行，谢了
例：表table1
id orderNo standardTime amount productNo
1 20090101 8 20 001
2 20090101 6 30 002
3 20090101 10 50 003
.
.
.
4 20090102 8 50 004
5 20090102 12 30 005
6 20090102 10 10 006
.
.
.
先分别统计orderNo 为20090101、20090102... 的standardTime*amount 的小计
然后统计所有orderNo 的standardTime*amount 德合计
如下：
id orderNo standardTime amount productNo perTotal
1 20090101 8 20 001 160
2 20090101 6 30 002 180
3 20090101 10 50 003 500
小计 840
.
.
4 20090102 8 50 004 400
5 20090102 12 30 005 360
6 20090102 10 10 006 100
小计 860
.
.
.
合计 1700
[解决办法]
- SQL code
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 e
from
#t
group by
a,b
with rollup
having grouping(b)=0 or grouping(a)=1
select
case when grouping(a)=1 then '合计' else cast(a as varchar) end a, b, c, sum(d) as d,sum(e) as e
from
#t
group by
a,b,c
with rollup
having grouping(c)=0 or grouping(a)=1
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 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 '合计' 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
drop table #t
/*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 行）
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 行）
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 行）
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 行）
*/
[解决办法]
- SQL code
------------------------------ Author :fredrickhu(小F，向高手学习)-- Date :2009-09-03 19:50:10-- Verstion:-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)--------------------------------> 测试数据：[tb]if object_id('[tb]') is not null drop table [tb]go create table [tb]([姓名] varchar(4),[学期] int,[语文] int,[数学] int,[英语] int,[政治] int)insert [tb]select '张三',1,70,60,80,30 union allselect '张三',2,80,90,75,40 union allselect '张三',3,50,70,85,60 union allselect '李四',1,66,80,90,55 union allselect '李四',2,75,70,85,65--------------开始查询--------------------------select case when grouping(姓名)=1 then '合计' else cast(姓名 as varchar) end 姓名, case when grouping(学期)=1 and grouping(姓名)=0 then '小计' else cast(学期 as varchar) end 学期, sum(语文) as 语文,sum(数学) as 数学,sum(英语) as 英语,sum(政治) as 政治from tb group by 姓名,学期 with rollup ------------------------------------------------------结果-------------------------------------------------------/*姓名 学期 语文 数学 英语 政治------------------------------ ------------------------------ ----------- ----------- ----------- -----------李四 1 66 80 90 55李四 2 75 70 85 65李四 小计 141 150 175 120张三 1 70 60 80 30张三 2 80 90 75 40张三 3 50 70 85 60张三 小计 200 220 240 130合计 NULL 341 370 415 250(8 行受影响) */
[解决办法]
- SQL code
-->Title:生成-->Author:wufeng4552-->Date :2009-09-10 16:26:59 declare @T table([id] int,[orderNo] Datetime,[standardTime] int,[amount] int,[productNo] nvarchar(3))Insert @Tselect 1,'20090101',8,20,N'001' union allselect 2,'20090101',6,30,N'002' union allselect 3,'20090101',10,50,N'003' union allselect 4,'20090102',8,50,N'004' union allselect 5,'20090102',12,30,N'005' union allselect 6,'20090102',10,10,N'006'select * from (select ltrim([id])[ID], [orderNo], [standardTime], [amount], [productNo], perTotal=standardTime*amountfrom @t union allselect '小',[orderNo],null,null,null,sum(standardTime*amount)from @tgroup by [orderNo]union allselect '',null,null,null,null,sum(standardTime*amount) from @t )t order by isnull([orderNo],99999),case when charindex('小',id)>0 then 1 else 0 end/*ID orderNo standardTime amount productNo perTotal------------ ----------------------- ------------ ----------- --------- -----------1 2009-01-01 00:00:00.000 8 20 001 1602 2009-01-01 00:00:00.000 6 30 002 1803 2009-01-01 00:00:00.000 10 50 003 500小 2009-01-01 00:00:00.000 NULL NULL NULL 8404 2009-01-02 00:00:00.000 8 50 004 4005 2009-01-02 00:00:00.000 12 30 005 3606 2009-01-02 00:00:00.000 10 10 006 100小 2009-01-02 00:00:00.000 NULL NULL NULL 860 NULL NULL NULL NULL 1700(9 料列受到影)*/
[解决办法]
- SQL code
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL DROP TABLE [tb]GOCREATE TABLE [tb]([id] int,[orderNo] NVARCHAR(8),[standardTime] int,[amount] int,[productNo] nvarchar(4))INSERT [tb]SELECT 1,'20090101',8,20,N'001' UNION ALLSELECT 2,'20090101',6,30,N'002' UNION ALLSELECT 3,'20090101',10,50,N'003' UNION ALLSELECT 4,'20090102',8,50,N'004' UNION ALLSELECT 5,'20090102',12,30,N'005' UNION ALLSELECT 6,'20090102',10,10,N'006'GO--SELECT * FROM [tb]-->SQL查询如下:SELECT id,orderNo,standardTime,amount,productNo,perTotal FROM ( SELECT *,perTotal=[standardTime]*[amount],px1=orderNo,px2=0 FROM tb a UNION ALL SELECT NULL,'小计',NULL,NULL,NULL,perTotal=SUM([standardTime]*[amount]),[orderNo],1 FROM tb GROUP BY [orderNo] UNION ALL SELECT NULL,'合计',NULL,NULL,NULL,perTotal=SUM([standardTime]*[amount]),'99999999',2 FROM tb ) AS tORDER BY px1,px2/*id orderNo standardTime amount productNo perTotal----------- -------- ------------ ----------- --------- -----------1 20090101 8 20 001 1602 20090101 6 30 002 1803 20090101 10 50 003 500NULL 小计 NULL NULL NULL 8404 20090102 8 50 004 4005 20090102 12 30 005 3606 20090102 10 10 006 100NULL 小计 NULL NULL NULL 860NULL 合计 NULL NULL NULL 1700(9 行受影响)*/
[解决办法]
- SQL code
declare @tb table (id int,orderno datetime,standardtime int,amount int,productno int)insert into @tb select 1,'20090101',8,20,1 union all select 2,'20090101',6,30,2 union all select 3,'20090101',10,50,3 union all select 4,'20090102',8,50,4 union all select 5,'20090102',12,30,5 union all select 6,'20090102',10,10,6;with china as( select *,pertotoal=standardtime*amount from @tb)select id= case when grouping(pertotoal)=0 then max(id) else '' end, orderno,pertotoal=sum(pertotoal), amout=case when grouping(pertotoal)=0 then MAX(amount) else '' end, productno=case when grouping(pertotoal)=0 then MAX(productno) else '' end from china group by orderno,pertotoal with rollup (6 行受影响)id orderno pertotoal amout productno----------- ----------------------- ----------- ----------- -----------1 2009-01-01 00:00:00.000 160 20 12 2009-01-01 00:00:00.000 180 30 23 2009-01-01 00:00:00.000 500 50 30 2009-01-01 00:00:00.000 840 0 06 2009-01-02 00:00:00.000 100 10 65 2009-01-02 00:00:00.000 360 30 54 2009-01-02 00:00:00.000 400 50 40 2009-01-02 00:00:00.000 860 0 00 NULL 1700 0 0(9 行受影响)