SQL SP 迭代算法

Question

我需要一点关于我的存储过程的帮助。

我想从结果中循环访问一组列，并获取每个值并查找相应的值，然后将所有这些值相加。

我能看到的唯一方法是为每个星期的头寸和每周的分数加上单独的选择语句使用单独的变量。谁能帮我？这显然不利于可扩展性。

商店程序

 @pName varchar(50) = ''
AS
BEGIN

  Declare @p1 int = 0
  Declare @p2 int = 0
  Declare @s1 int = 0
  Declare @s2 int = 0
  Declare @pUserID int = 0
  Declare @pTotal int = 0

  set @pUserID = (Select UserID from PKR_USERS where UserName = @pName)
  set @p1 = (Select WEEK1POS From PKR_TOURNAMENT12)
  set @p2 = (Select WEEK2POS From PKR_TOURNAMENT12)
  set @s1  = (Select Points from PKR_SCORING where Position = @p1)
  set @s2  = (Select Points from PKR_SCORING where Position = @p2)
  set @pTotal = @s1 + @s2
  Update PKR_RESULTS set points = @pTotal Where UserID =  @pUserID
END

> PKR_Tournament12 Table 
> 
> [USERID][WEEK1POS][WEEK2POS] 1    1   1

PKR_Scoring表

[

位置][积分] 1 10 2 9

提前致谢

Answer 1

嗨，

试试这个我用了 UNPIVOT 请测试它

Declare @pTotal int = ISNULL(
  SELECT SUM(ISNULL(Points)) FROM PKR_SCORING where Position  IN (
   SELECT FieldValue
FROM
(
  SELECT 
     WEEK1POS ,
     WEEK2POS 
  FROM dbo.PKR_TOURNAMENT12 WHERE USERID=(Select UserID from PKR_USERS where UserName = @pName)
) MyTable
 UNPIVOT
(FieldValue FOR FieldCode IN (WEEK1POS, WEEK2POS))AS MyUnPivot),0)
Update PKR_RESULTS set points = @pTotal Where UserID =  @pUserID