动态检索

[chenyihong]

我现在有一个表Table11，其中有12个字段imoney1,imoney2...imoney11,imoney12。

假如我传进去的参数为1，则求sum(imoney1);传进去参数2，则求sum(imoney1)+sum(imoney2)；
依此类推，传进12就求全部的和。

请问：如何用一个简单的循环来实现。
谢谢！

[liangCK]

CREATE PROC dbo.p
   @tb_name sysname,
   @iMoney int
AS
   DECLARE @column VARCHAR(1000);
   SET @column='';
   
   SELECT @column=@column+'+SUM(['+name+'])'
   FROM sys.columns
   WHERE object_id=OBJECT_ID(@tb_name)
       AND column_id<=@iMoney
   ORDER BY column_id;
   
   SET @column=STUFF(@column,1,1,'');
   
   EXEC ('SELECT '+@column+' AS SumMoney FROM ['+@tb_name+']');
GO

[中国风]

declare @i int,@s nvarchar(4000)
set @i=2
set @s=''
while @i>0
	select @s='+sum([imoney'+rtrim(@i)+'])'+@s,@i=@i-1
set @s=stuff(@s,1,1,'')
--print 'select '+@s+' from Table11' --显示语句

exec('select '+@s+' from Table11')

[fcuandy]

CREATE TABLE table1(f1 INT,f2 INT,f3 INT,f4 INT,f5 INT,f6 INT,f7 INT,f8 INT,f9 INT,f10 INT,f11 INT,f12 INT)
GO

INSERT table1 SELECT 1,2,3,4,4,5,6,6,67,7,7,0
UNION ALL     SELECT 2,3,4,54,6,7,2,4,5,6,1,1
GO


DECLARE @s VARCHAR(8000)

SELECT @s=ISNULL(@s+'+','') + 'SUM(f' + RTRIM(id) + ')' FROM
	(
		SELECT 1 id
		UNION ALL
		SELECT 2
		UNION ALL
		SELECT 3
		UNION ALL
		SELECT 4
		UNION ALL
		SELECT 5
		UNION ALL
		SELECT 6
		UNION ALL
		SELECT 7
		UNION ALL
		SELECT 8
		UNION ALL
		SELECT 9
		UNION ALL
		SELECT 10
		UNION ALL
		SELECT 11
		UNION ALL
		SELECT 12
	) x WHERE id<=5  --这里改为你要传入的值即可

EXEC('SELECT ' + @s + ' FROM table1')


DROP TABLE table1
GO

[fcuandy]

sys.columns或sysindexes里col的顺序未必与col名是对应的...

[rucypli]

create proc loop
@count int
as
declare @num
set @num=1
while @num<=@count
begin
exec ('select @sum=sum(imoney_' + @count + ')+@sum from table11"
set @num= @num+1
end
select @sum