我有一个像这样的CTE:
WITH CTE(PartNum, RowIndex) AS
(
-- Anchor Member
SELECT
PartNum
,CAST(OurQty AS INT) AS RowIndex
FROM dbo.RcvDtl
WHERE OurQty > 0
UNION ALL
-- Recursive Member
SELECT
PartNum
,RowIndex - 1
FROM CTE
WHERE RowIndex - 1 > 0
)
SELECT
tbl1.PONum, tbl1.PartNum,
CAST(tbl1.OurQty AS INT) AS Quantity,
tbl2.RowIndex AS RowIndex
FROM
dbo.RcvDtl AS Tbl1
INNER JOIN
CTE AS Tbl2 ON Tbl1.PartNum = Tbl2.PartNum
WHERE
tbl1.PONum = '63'
ORDER BY
Tbl1.PartNum OPTION(MAXRECURSION 1000)
这是运行在以下表格上的:
| PartNum | Quantity
---------------------
10050857 6
10050886 10
10050923 24
该查询的意图是基于其数量复制记录(PartNum)。该查询将第一个PartNum“10050857”复制46次(6 +(6 + 10 + 24)),而实际上只应该复制6次,第二个PartNum“10050886”按预期复制10次,最后一个PartNum“10050923”按预期复制24次。
为什么该查询会将第一条记录累加到其后面的记录中进行复制?