表值函数中的顺序错误（保持递归 CTE 的“顺序”）

Question

几分钟前，我问这里如何使用递归CTE获取父记录。 现在可以工作了，但是当我创建一个返回所有父项的表值函数时，我得到了错误的顺序（向后，按 PK idData 排序）。我无法直接订购，因为我需要 CTE 提供的逻辑顺序。

这给出了正确的顺序（从下一个父级到该父级等等）：

declare @fiData int;
set @fiData=16177344;
WITH PreviousClaims(idData,fiData) 
AS(
    SELECT parent.idData,parent.fiData
    FROM tabData parent
    WHERE parent.idData = @fiData

    UNION ALL

    SELECT child.idData,child.fiData
    FROM tabData child
    INNER JOIN PreviousClaims parent ON parent.fiData = child.idData
)
select iddata from PreviousClaims

但是以下函数以向后顺序返回所有记录（按 PK 排序）：

CREATE FUNCTION [dbo].[_previousClaimsByFiData] (
    @fiData INT
)

RETURNS @retPreviousClaims TABLE 
(
    idData int PRIMARY KEY NOT NULL
)
AS 
BEGIN
    DECLARE @idData int;

    WITH PreviousClaims(idData,fiData) 
    AS(
        SELECT parent.idData,parent.fiData
        FROM tabData parent
        WHERE parent.idData = @fiData

        UNION ALL

        SELECT child.idData,child.fiData
        FROM tabData child
        INNER JOIN PreviousClaims parent ON parent.fiData = child.idData
    )

    INSERT INTO @retPreviousClaims
        SELECT idData FROM PreviousClaims;
    RETURN;
END;

select * from dbo._previousClaimsByFiData(16177344);

---

更新： 由于每个人都认为 CTE 不是有序的（任何“排序”都将是完全任意和巧合的），我想知道为什么相反的情况似乎是正确的。我向许多父母查询了儿童索赔，CTE 中的顺序正是我从孩子到父母等时的逻辑顺序。这意味着 CTE 像游标一样从一个记录迭代到另一个记录，并且以下 select 完全按照此顺序返回它。但是当我调用 TVF 时，我得到了主键 idData 的顺序。

解决方案很简单。我只需要删除 TVF 返回表的父键。因此，将...更改

RETURNS @retPreviousClaims TABLE 
(
   idData int PRIMARY KEY NOT NULL
)

为...

RETURNS @retPreviousClaims TABLE 
(
     idData int
)

..，它会保持正确的“顺序”（与插入 CTE 临时结果集中的顺序相同）。

更新2： 因为 Damien 提到“CTE-Order”在某些情况下可能会发生变化，所以我将在 CTE 中添加一个新列 relationLevel 来描述父记录的关系级别（顺便说一句，这完全是）对于 ssas 立方体来说一般有用）。 所以最终的 Inline-TVF（返回所有列）现在是：

CREATE FUNCTION [dbo].[_previousClaimsByFiData] (
    @fiData INT
)

RETURNS TABLE AS
RETURN(
    WITH PreviousClaims 
    AS(
        SELECT 1 AS relationLevel, child.*
        FROM tabData child
        WHERE child.idData = @fiData

        UNION ALL

        SELECT relationLevel+1, child.*
        FROM tabData child
        INNER JOIN PreviousClaims parent ON parent.fiData = child.idData
    )

    SELECT TOP 100 PERCENT * FROM PreviousClaims order by relationLevel
)

这是一个示例关系：

select idData,fiData,relationLevel from dbo._previousClaimsByFiData(46600314);

谢谢。

Answer 1

进行 ORDERing 的正确方法是将 ORDER BY 子句添加到最外层的选择中。其他任何事情都依赖于可能随时更改的实现细节（包括数据库/表的大小是否增加，这可能允许发生更多并行处理）。

如果您需要方便的方式来进行排序，请查看 WITH 上的 MSDN 页面：

WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel
    FROM dbo.MyEmployees 
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1
    FROM dbo.MyEmployees AS e
        INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID 
)

将类似内容添加到 CTE 的 EmployeeLevel 列中，一切都应该有效。

Answer 2

我认为 CTE 正在创建排序的印象是错误的。巧合的是，行按顺序出现（可能是由于它们最初插入 tabData 的方式）。无论如何，TVF 返回一个表，因此如果您想保证排序，则必须将 ORDER BY 显式添加到您用来调用它的 SELECT 中：

select * from dbo._previousClaimsByFiData(16177344) order by idData

Answer 3

看不到任何 ORDER BY - 无论是在表值函数中，还是在该 TVF 的 SELECT 中。

任何“排序”都将是完全任意和巧合的。

如果您想要特定的订单，则需要指定 ORDER BY。

那么为什么不能将 ORDER BY 添加到 SELECT 中：

 SELECT * FROM dbo._previousClaimsByFiData(16177344) 
 ORDER BY (whatever you want to order by)....

或者将 ORDER BY 放入 TVF 中：

INSERT INTO @retPreviousClaims
    SELECT idData FROM PreviousClaims
    ORDER BY idData DESC (or whatever it is you want to order by...)