数据累计问题

[caoqizi]

请问各位,我要实现一个类似下面的功能如何完成?
表TABLE1 起中数据为
COM1 COM2 COM3 COM4 COM5
2 23 2 6 6
33 12 3 18 23

其我想得到的结果是在倒数第二行可以是个列的小计合,最后一行得到累计数据结果如下.
COM1 COM2 COM3 COM4 COM5
2 23 2 6 6
33 12 3 18 23
35 35 5 24 29
35 70 75 99 128
第二行我已经实现了,可是不知道最后这行如何实现,还请各位帮帮忙.在这里先谢谢各位了.

[lsxaa]

select ...
...
...
union all
select com1,com1+com2,com1+com2+com3...

[子陌红尘]

select COM1, COM2, COM3, COM4, COM5 from TABLE1
union all
--倒数第二行
select sum(COM1), sum(COM2), sum(COM3), sum(COM4), sum(COM5) from TABLE1
union all
--最后一行
select sum(COM1),
sum(COM1)+sum(COM2),
sum(COM1)+sum(COM2)+sum(COM3),
sum(COM1)+sum(COM2)+sum(COM3)+sum(COM4),
sum(COM1)+sum(COM2)+sum(COM3)+sum(COM4)+sum(COM5) from TABLE1

[vinsonshen]

来迟了~~~

[caoqizi]

這樣的對於確定的字段可以, 可是我的字段是不定的增加的...其中還有一部分是字符型的.所以沒想到好的方法.
請各為在幫幫忙.謝謝.

[caoqizi]

沒人來了啊...55`````

[caoqizi]

字符的全部為 null 就可以了.

[mingyang47]

我不会，我是新手。
希望大家多多帮帮我，谢谢了。

[zjcxc]

--如果字符型字段跳过不处理合计,你可以这样写动态语句:
create proc p_qry
@tbname sysname --要统计的表名
as
declare @s1 nvarchar(4000),@s2 nvarchar(4000),@s3 nvarchar(4000),@s4 nvarchar(4000)
select @s1='',@s2='',@s3='',@s4=''
select @s1=@s1+',['+a.name+']'
,@s2=@s2+','+case
when b.name like '%int'
or b.name like '%money'
or b.name in('decimal','numeric','float','real')
then 'sum(['+a.name+'])'
else 'null' end
,@s3=@s3+case
when b.name like '%int'
or b.name like '%money'
or b.name in('decimal','numeric','float','real')
then '+sum(['+a.name+'])'
else '' end
,@s4=@s4+','+case
when b.name like '%int'
or b.name like '%money'
or b.name in('decimal','numeric','float','real')
then stuff(@s3,1,1,'')
else 'null' end
from syscolumns a,systypes b
where a.id=object_id(@tbname) and a.xusertype=b.xusertype
order by a.colid
select @s1=stuff(@s1,1,1,'')
,@s2=stuff(@s2,1,1,'')
,@s4=stuff(@s4,1,1,'')
exec('
select '+@s1+' from ['+@tbname+']
union all
select '+@s2+' from ['+@tbname+']
union all
select '+@s4+' from ['+@tbname+']
')
go

--调用
exec p_qry 'systypes'

[zjcxc]

我是把字符型的处理为空,如果是处理为null,用我最后一这种.

[caoqizi]

好的. 我试试 谢谢

[caoqizi]

能给我说说思路吗? 我有点看不大懂.

[zjcxc]

其中還有一部分是字符型的,那对于字符型的字段,你要求怎么处理?

[zjcxc]

--如果字符型字段跳过不处理合计,你可以这样写动态语句:
create proc p_qry
@tbname sysname --要统计的表名
as
declare @s1 nvarchar(4000),@s2 nvarchar(4000),@s3 nvarchar(4000),@s4 nvarchar(4000)
select @s1='',@s2='',@s3='',@s4=''
select @s1=@s1+',['+a.name+']'
,@s2=@s2+','+case
when b.name like '%int'
or b.name like '%money'
or b.name in('decimal','numeric','float','real')
then 'sum(['+a.name+'])'
else '''''' end
,@s3=@s3+case
when b.name like '%int'
or b.name like '%money'
or b.name in('decimal','numeric','float','real')
then '+sum(['+a.name+'])'
else '' end
,@s4=@s4+','+case
when b.name like '%int'
or b.name like '%money'
or b.name in('decimal','numeric','float','real')
then stuff(@s3,1,1,'')
else '''''' end
from syscolumns a,systypes b
where a.id=object_id(@tbname) and a.xusertype=b.xusertype
order by a.colid
select @s1=stuff(@s1,1,1,'')
,@s2=stuff(@s2,1,1,'')
,@s4=stuff(@s4,1,1,'')
exec('
select '+@s1+' from ['+@tbname+']
union all
select '+@s2+' from ['+@tbname+']
union all
select '+@s4+' from ['+@tbname+']
')
go

--调用
exec p_qry 'systypes'