sql - 在CTE查询sqlserver 2008中初始化和递增变量

我正在使用sqlserver 2008，我想在第二部分(问题行)中同时初始化和递增变量(@NUMTwo)。

我正在创建一个CTE查询。

这可能吗，如果可以，请告诉我。

以下是一个示例example.I希望我很清楚。

CREATE table #TempTable
(
childProductID  INT,parentProductID INT,productModel varchar(50),[Num2] VARCHAR(100)
)

DECLARE @NUMTwo  INT = 0
WITH tableR   AS
 (

        -- First Part
    SELECT childProductID = null,parentProductID=null,productModel  from Products where productid in (@a),[Num2] = convert(varchar(100), '')
            UNION ALL
            --Second Part
    SELECT e.childProductID,e.parentProductID,prd.productModel FROM ProductIncludes AS e
            ,[Num2] = convert(varchar(100),'1.'  + @NUMTwo+=1 )    -- Problem line
    INNER JOIN  Products AS PRD ON e.childProductID = PRD.productID
    WHERE parentProductID in (@a)

      )

INSERT INTO #TempTable(childProductID,parentProductID,productModel,[Num2])
SELECT childProductID,parentProductID,productModel,[Num2]

END
SELECT * FROM #TempTable

最佳答案

如果参数@NUMTwo仅用于编号行，则可以使用ROW_NUMBER() OVER(...)代替它，如下所示:

WITH tableR   AS
 (
    SELECT childProductID = NULL, parentProductID = NULL,
      productModel, NUMTwo = CAST('0' AS VARCHAR(10))
    FROM Products
    WHERE
      productid in (@a),
      [Num2] = convert(varchar(100), '')
    UNION ALL
    SELECT e.childProductID, e.parentProductID,
      prd.productModel,
      NUMTwo = '1.' +
        CAST( ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS VARCHAR(10))
    FROM ProductIncludes AS e
    INNER JOIN  Products AS PRD ON e.childProductID = PRD.productID
    WHERE parentProductID in (@a)
)