SQL SERVER存储过程里取到一个list,如何在存储过程里再循环读取

[gongjie416]

存储过程

select roomid from room_info 先是取到所有roomid,
然后我想在接下中,根据每个roomid循环更新或者删除其他数据,
想请教 如何在存储过程里得到每一个roomid

[spiritofdragon]

用游标，在SQL帮助里搜下 cursor

[發糞塗牆]

如果你的roomid没有什么顺序可言，那可以加个自增ID，比如2005以后使用row_number()来自增，然后按这个id循环一个一个处理。

[gongjie416]

没用过游标呀,有没有例子借鉴下

[汤姆克鲁斯]

-->分组的聚合乘积
--> 测试数据：[groups]
IF OBJECT_ID('[groups]') IS NOT NULL DROP TABLE [groups]
GO
CREATE TABLE [groups]([groupid] VARCHAR(1),ordmonth DATETIME,[val] INT)
INSERT [groups]
SELECT 'a','2012-01-01',3 UNION ALL
SELECT 'a','2012-02-01',1 UNION ALL
SELECT 'a','2012-03-01',8 UNION ALL
SELECT 'a','2012-04-01',10 UNION ALL
SELECT 'a','2012-05-01',15 UNION ALL
SELECT 'b','2012-01-01',1 UNION ALL
SELECT 'b','2012-02-01',2 UNION ALL
SELECT 'b','2012-03-01',3 UNION ALL
SELECT 'b','2012-04-01',20 UNION ALL
SELECT 'b','2012-05-01',10 UNION ALL
SELECT 'c','2012-01-01',10 UNION ALL
SELECT 'c','2012-02-01',12 UNION ALL
SELECT 'c','2012-03-01',13 UNION ALL
SELECT 'c','2012-04-01',14 UNION ALL
SELECT 'c','2012-04-01',3 UNION ALL
SELECT 'c','2012-05-01',20 
GO

--> 测试语句：
DECLARE @result TABLE ([groupid] VARCHAR(1),[product] BIGINT)
DECLARE @groupid VARCHAR(1),@val INT,@prvgroupid VARCHAR(1),@product BIGINT

DECLARE c CURSOR FAST_FORWARD 
FOR
SELECT [groupid],[val] FROM [groups] ORDER BY [groupid]
OPEN c
FETCH NEXT FROM c INTO @groupid, @val
SELECT @prvgroupid=@groupid,@product=1
WHILE @@fetch_status = 0
BEGIN 
	IF @groupid<>@prvgroupid
	BEGIN 
		INSERT INTO @result VALUES(@prvgroupid,@product)
		SELECT @prvgroupid=@groupid,@product=1
	END
	SET @product=@product*@val
	FETCH NEXT FROM c INTO @groupid, @val
END	
IF @prvgroupid IS NOT NULL
	INSERT INTO @result VALUES(@prvgroupid,@product)
CLOSE c
DEALLOCATE c

SELECT [groupid],[product] FROM @result

--方法二
--这个方法看似很简洁，语法精炼，但是如果，在数据中出现
SELECT [groupid],EXP(SUM(LOG([val]))) FROM [groups]
GROUP BY [groupid]

[汤姆克鲁斯]

-->连续聚合
-->测试数据：[groups]
--SELECT [groupid],[ordmonth],[val] FROM [groups]
DECLARE @result TABLE ([groupid] VARCHAR(1),[ordmonth] DATETIME,[val] INT,[runval] INT)
DECLARE @groupid VARCHAR(1),@prvgroupid VARCHAR(1),@ordmonth DATETIME,@val INT,@runval INT

DECLARE c CURSOR FAST_FORWARD 
FOR
SELECT [groupid],[ordmonth],[val] FROM [groups] ORDER BY [groupid],[ordmonth]
OPEN c
FETCH NEXT FROM c INTO @groupid,@ordmonth,@val
SELECT @prvgroupid=@groupid,@runval=0
WHILE @@fetch_status = 0
BEGIN 
	IF @groupid<>@prvgroupid
	BEGIN 
		SELECT @prvgroupid=@groupid,@runval=0
	END
	SET @runval=@runval+@val
	INSERT INTO @result VALUES(@prvgroupid,@ordmonth,@val,@runval)
	FETCH NEXT FROM c INTO @groupid,@ordmonth,@val
END	
CLOSE c
DEALLOCATE c

SELECT [groupid],[ordmonth],[val],[runval]FROM @result ORDER BY [groupid],[ordmonth] 

[汤姆克鲁斯]

http://msdn.microsoft.com/zh-cn/library/ms180169.aspx