在报表数据统计的时候,用到存储过程来统计1到12月份的金额汇总,通过制定的月份累计,比如截止到12月份,就统计1到12月份的金额。这种写法可以这样实现
,贴下代码
DECLARE @i INT,@NewMonth INT,@COLUMN VARCHAR(1000),@SumCOLUMN VARCHAR(2000),@sql VARCHAR(6000)
SET @i=0;SET @COLUMN='';SET @SumCOLUMN='';SET @sql='';
SET @NewMonth=12
WHILE @i<@NewMonth
BEGIN
SET @i=@i+1
SET @COLUMN='ISNULL(SUM(Amount'+CAST(@i AS VARCHAR(10))+'),0)'
SET @SumCOLUMN=@SumCOLUMN+'+'+@COLUMN
END
SET @SumCOLUMN=STUFF(@SumCOLUMN,1,1,''); --截取第1个字符替换为空字符(用于删除第一个,)
SELECT @SumCOLUMN
执行结果为ISNULL(SUM(Amount1),0)+ISNULL(SUM(Amount2),0)+ISNULL(SUM(Amount3),0)+ISNULL(SUM(Amount4),0)+ISNULL(SUM(Amount5),0)+ISNULL(SUM(Amount6),0)+ISNULL(SUM(Amount7),0)+ISNULL(SUM(Amount8),0)+ISNULL(SUM(Amount9),0)+ISNULL(SUM(Amount10),0)+ISNULL(SUM(Amount11),0)+ISNULL(SUM(Amount12),0)