：从子状态获取主状态

Question

我在CTE中找到了一个很好的SQL递归示例，但未能将它应用到我的表中：

• http://walkingoncoals.blogspot.de/2009/12/fun-with-recursive-sql-part-1.html

我有下表(ObjectStates)：

ID    Title    ParentID
1     Draft    null
2     Green    null
3     Red      null
4     Foo      1
5     Bar      4

我正在尝试创建一个函数，在查询时返回“主”状态。示例：

GetMainState(5)
-- Shall return 1
GetMainState(4)
-- Shall return 1
GetMainState(2)
-- Shall return 2

我到目前为止：

CREATE FUNCTION [dbo].[GetMainObjectState] (@ObjectStateID INT)
RETURNS TABLE
AS
RETURN 
(
 WITH StateRecurcsion(ID, ParentID, Level) AS
       (
           SELECT ID, ParentID, 0
           FROM ObjectStates
           WHERE ID = @ObjectStateID
           UNION ALL
           SELECT uOS.ID, uOS.ParentID, sOS.Level+1
           FROM ObjectStates uOS, StateRecurcsion sOS
           WHERE uOS.ParentID= sOS.ID
       )
 SELECT os.ID, os.Title, sos.Level
 FROM ObjectStates os, StateRecurcsion sos
 WHERE os.ID = sos.ID
)
GO

我试图像上面所示的教程一样创建函数，但不知怎么的，我没有得到正确的结果。

Answer 1

您可以创建一个包含"root“值的CTE，然后在您的函数中查询它，例如：

;WITH CTEHierarchy
AS (
    SELECT 
    ID
        ,0 AS LEVEL
        ,ID AS root

    FROM ObjectStates
    WHERE  ParentID IS NULL

    UNION ALL

    SELECT 
    ObjectStates.ID
        ,LEVEL + 1 AS LEVEL
        ,[root]

    FROM ObjectStates
    INNER JOIN CTEHierarchy uh ON uh.id = ObjectStates.ParentID
    )    
    SELECT [root]
    FROM CTEHierarchy
    WHERE ID = @ObjectStateID