表值函数中的顺序错误（保持递归CTE的“顺序”） [英] Wrong order in Table valued Function(keep "order" of a recursive CTE)

问题描述

几分钟前，我问了：

 ，其中DirectReports（ManagerID，EmployeeID，Title，EmployeeLevel）为AS 
（
 SELECT ManagerID，EmployeeID，Title，0 AS EmployeeLevel 
从dbo.MyEmployees 
 WHERE ManagerID为NULL 
 UNION ALL 
 SELECT e.ManagerID，e .EmployeeID，e.Title，EmployeeLevel + 1 
来自dbo.MyEmployees AS e 
内联DirectDirects AS d d 
 e.ManagerID = d.EmployeeID 
）
  

在EmployeeLevel列的CTE中添加一些类似的东西，一切都会正常工作。

a few minutes ago i asked here how to get parent records with a recursive CTE. This works now, but I get the wrong order(backwards, ordered by the PK idData) when i create a Table valued Function which returns all parents. I cannot order directly because i need the logical order provided by the CTE.

This gives the correct order(from next parent to that parent and so on):

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

But the following function returns all records in backwards order(ordered by 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);

---

UPDATE: Since everybody beliefs that the CTE is not ordering(Any "ordering" will be totally arbitrary and coincidental), i'm wondering why the opposite seems to be true. I have queried a child claim with many parents and the order in the CTE is exactly the logical order when i go from child to parent and so on. This would mean that the CTE is iterating from record to record like a cursor and the following select returns it in exact this order. But when i call the TVF i got the order of the primary key idData instead.

The solution was simple. I only needed to remove the parent key of the return-Table of the TVF. So change...

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

to...

RETURNS @retPreviousClaims TABLE 
(
     idData int
)

.. and it keeps the right "order" (same order they were inserted into the CTE's temporary result set).

UPDATE2: Because Damien mentioned that the "CTE-Order" could change in certain circumstances, i will add a new column relationLevel to the CTE which describes the level of relationship of the parent records (what is by the way quite useful in general f.e. for a ssas cube). So the final Inline-TVF(which returns all columns) is now:

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
)

This is an exemplary relationship:

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

Thank you.

解决方案

The correct way to do your ORDERing is to add an ORDER BY clause to your outermost select. Anything else is relying on implementation details that may change at any time (including if the size of your database/tables goes up, which may allow more parallel processing to occur).

If you need something convenient to allow the ordering to take place, look at Example D in the examples from the MSDN page on WITH:

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 
)

Add something similay to the EmployeeLevel column to your CTE, and everything should work.

这篇关于表值函数中的顺序错误（保持递归CTE的“顺序”）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！