TSQL计算多个字段的总和 [英] TSQL calculating sum of numerous fields

问题描述

我有这种数据:

Date         Count1 Count2 Count3 ... Countxx
01-05-2012   1      0      1          2
01-05-2012   2      1      3          0
01-05-2012   2      3      3          1
02-05-2012   1      3      2          0
02-05-2012   5      2      0          0

我需要计算按日期分组的各个字段(Count1到Countxx)的总和，并编写以下SQL:

select sum(count1), sum(count2), sum(count3), .. , sum(countxx) 
from table1 group by date

我的第一个问题:SQL Server中有什么方法可以自动执行此操作(不知道字段数，因为每次字段的名称和数量都会不同，因此手动编写SQL非常麻烦). /p>

第二，如何从当前行减去前一行以及前7行的平均值来计算值?

谢谢！

解决方案

create procedure USP_FindSum @tablename varchar(100)                
as
begin
 create table #temp(id int identity(1,1),name varchar(100))
 declare @sqlcmd nvarchar(max)=''
 SET @sqlcmd= N'Insert into #temp select name from sys.columns col_table where        
 col_table.object_id=object_id('''+@tablename+''')'
 EXEC sp_executesql @sqlcmd
 declare @sqlseg varchar(max)=''
 declare @tempcount int
 declare @i int=1
 select @tempcount=COUNT(id) from #temp
 while(@i<=@tempcount)
 BEGIN
   declare @CName varchar(100)
   SELECT @CName= name from #temp where id=@i
   if(@i!=@tempcount)
    SET @sqlseg=+@sqlseg+'sum('+@CName+')'+',' 
   else
    SET @sqlseg =+@sqlseg+'sum('+@CName+')'
   SET @i=@i+1
 END
 SET @sqlcmd=N'select '+@sqlseg+' from '+@tablename
 EXEC sp_executesql @sqlcmd
 DROP TABLE #temp
END

假设表中的所有列都是可累加的.由于您的要求很奇怪，因此这种解决方法也可能如此.

只需将表名作为参数传递并执行，

Exec USP_FindSum '<tablename here>'

I have this kind of data:

Date         Count1 Count2 Count3 ... Countxx
01-05-2012   1      0      1          2
01-05-2012   2      1      3          0
01-05-2012   2      3      3          1
02-05-2012   1      3      2          0
02-05-2012   5      2      0          0

and I need to calculate sum of respective fields (Count1 to Countxx) grouped by date and wrote this SQL:

select sum(count1), sum(count2), sum(count3), .. , sum(countxx) 
from table1 group by date

my first question: is there any way in SQL server to do this automatically (without knowing number of fields, since the name and number of the fields will be different each time, thus making writing the SQL manually very cumbersome).

secondly, how to calculate value from current row minus previous row, and average of previous 7 rows?

Thanks!

解决方案

create procedure USP_FindSum @tablename varchar(100)                
as
begin
 create table #temp(id int identity(1,1),name varchar(100))
 declare @sqlcmd nvarchar(max)=''
 SET @sqlcmd= N'Insert into #temp select name from sys.columns col_table where        
 col_table.object_id=object_id('''+@tablename+''')'
 EXEC sp_executesql @sqlcmd
 declare @sqlseg varchar(max)=''
 declare @tempcount int
 declare @i int=1
 select @tempcount=COUNT(id) from #temp
 while(@i<=@tempcount)
 BEGIN
   declare @CName varchar(100)
   SELECT @CName= name from #temp where id=@i
   if(@i!=@tempcount)
    SET @sqlseg=+@sqlseg+'sum('+@CName+')'+',' 
   else
    SET @sqlseg =+@sqlseg+'sum('+@CName+')'
   SET @i=@i+1
 END
 SET @sqlcmd=N'select '+@sqlseg+' from '+@tablename
 EXEC sp_executesql @sqlcmd
 DROP TABLE #temp
END

Assuming all the columns in the table are summable. As your requirement is weird this workaround may also be so.

Just pass the table name as parameter and execute,

Exec USP_FindSum '<tablename here>'

这篇关于TSQL计算多个字段的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！