你要得到的结果是要40个字段,还是一个总的汇总字段阿,如果是40个汇总的字段,不管怎么写,你都要有40个sum,只是在写的时候可以有一些办法偷懒,但最后的结果还是要有40个sum的。 比如: DECLARE @I INT DECLARE @S VARCHAR(2000) SELECT @I=2,@S='SUM(pay_1)' WHILE @I<=40 BEGIN SELECT @S=@S+',SUM(pay_'+RTRIM(CAST(@I AS VARCHAR(3)))+')' SELECT @I=@I+1 END EXEC('SELECT '+@S+' FROM TABLENAME')
这样也不会簡单:set @sql nvarchar(500) set @sql='select ' set @i=1 while @i<=40 begin set @sql=@sql+'sum(isnull(pay_'+convert(char(2),@i)+',0)),' set @i=@i+1 end set @sql=right(@sql,len(@sql-1))+' from 工资表' exec(@sql)
declare @i int declare @sql nvarchar(500) set @sql='select ' set @i=1 while @i<=40 begin set @sql=@sql+'sum(isnull(pay_'+convert(char(2),@i)+',0)),' set @i=@i+1 end set @sql=right(@sql,len(@sql-1))+' from 工资表' exec(@sql)
比如:
DECLARE @I INT
DECLARE @S VARCHAR(2000)
SELECT @I=2,@S='SUM(pay_1)'
WHILE @I<=40
BEGIN
SELECT @S=@S+',SUM(pay_'+RTRIM(CAST(@I AS VARCHAR(3)))+')'
SELECT @I=@I+1
END
EXEC('SELECT '+@S+' FROM TABLENAME')
set @sql='select '
set @i=1
while @i<=40
begin
set @sql=@sql+'sum(isnull(pay_'+convert(char(2),@i)+',0)),'
set @i=@i+1
end
set @sql=right(@sql,len(@sql-1))+' from 工资表'
exec(@sql)
declare @sql nvarchar(500)
set @sql='select '
set @i=1
while @i<=40
begin
set @sql=@sql+'sum(isnull(pay_'+convert(char(2),@i)+',0)),'
set @i=@i+1
end
set @sql=right(@sql,len(@sql-1))+' from 工资表'
exec(@sql)