动态检索
我现在有一个表Table11，其中有12个字段imoney1,imoney2...imoney11,imoney12。
假如我传进去的参数为1，则求sum(imoney1);传进去参数2，则求sum(imoney1)+sum(imoney2)；
依此类推，传进12就求全部的和。
请问：如何用一个简单的循环来实现。
谢谢！
假如我传进去的参数为1，则求sum(imoney1);传进去参数2，则求sum(imoney1)+sum(imoney2)；
依此类推，传进12就求全部的和。
请问：如何用一个简单的循环来实现。
谢谢！
...全文
88 5 打赏 收藏 转发到动态 举报
分享
配置赞助广告
请发表友善的回复…
发表回复
打赏红包 
rucypli 2008-10-20
- 打赏
- 举报
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
@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
fcuandy 2008-10-20
- 打赏
- 举报
sys.columns或sysindexes里col的顺序未必与col名是对应的...
fcuandy 2008-10-20
- 打赏
- 举报
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
中国风 2008-10-20
- 打赏
- 举报
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')
liangCK 2008-10-20
- 打赏
- 举报
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