CTE SQL查询获取完整路径

Question

我有一个简单的查询，

WITH  conn_cte ( ParentCategoryId, CategoryId, IdsPath )
        AS ( SELECT ParentCategoryId
                   ,CategoryId
                   ,CAST(ParentCategoryId AS varchar(1000))
             FROM   Ind_CategoriesConnections
             WHERE  ParentCategoryId = 0
             UNION ALL
             SELECT cc.ParentCategoryId
                   ,cc.CategoryId
                   ,CAST(IdsPath + ','
                    + CAST (cc.ParentCategoryId AS varchar(5)) AS varchar(1000))
             FROM   Ind_CategoriesConnections AS cc
                    INNER JOIN conn_cte AS conn ON conn.CategoryId = cc.ParentCategoryId
                                                   AND cc.categoryid NOT IN (
                                                   SELECT conn.Categoryid )
           )
  SELECT  x.*
  FROM    ( SELECT  t.ParentCategoryId
                   ,t.CategoryId
                   ,t.IdsPath + ',' + CAST(t.CategoryId AS varchar(5)) AS [path]
            FROM    conn_cte t
                    INNER JOIN Ind_Categories c ON t.CategoryId = c.CategoryId
                                                   AND c.CategoryViewId = 1
                                                   AND c.IsActiveYN = 1
          ) x
  ORDER BY x.path

我对查询（最佳）感兴趣，它只返回从根到叶的完整路径。

例如，结果为零的部分

Parent  Child   Path
12       16     0,8,12,16
16       17     0,8,12,16,17
17       18     0,8,12,16,17,18
17       19     0,8,12,16,17,19

是根 18,19 是叶子（和子节点），我想部分忽略像 0,8,12,16 和 0,8,12 这样的路径,16,17 并仅获取完整路径（以叶子结尾） 0,8,12,16,17,18 和 0,8,12,16,17,19

Answer 1

DECLARE @tbl TABLE
  ( 
   Id int
  ,ParentId int
  )

INSERT  INTO @tbl
        ( Id, ParentId )
VALUES  ( 0, NULL )
,       ( 8, 0 )
,       ( 12, 8 )
,       ( 16, 12 )
,       ( 17, 16 )
,       ( 18, 17 )
,       ( 19, 17 )

;
WITH  abcd
        AS (
              -- anchor
            SELECT   id
                    ,ParentID
                    ,CAST(id AS VARCHAR(100)) AS [Path]
            FROM    @tbl
            WHERE   ParentId IS NULL
            UNION ALL
              --recursive member
            SELECT  t.id
                   ,t.ParentID
                   ,CAST(a.[Path] + ',' + CAST( t.ID AS VARCHAR(100)) AS varchar(100)) AS [Path]
            FROM    @tbl AS t
                    JOIN abcd AS a ON t.ParentId = a.id
           )
SELECT  Id ,ParentID ,[Path]
FROM    abcd
WHERE   Id NOT IN ( SELECT  ParentId
                    FROM    @tbl
                    WHERE   ParentId IS NOT NULL )

返回

Id          ParentID    Path
----------- ----------- ----------------------
18          17          0,8,12,16,17,18
19          17          0,8,12,16,17,19

语法为 SQL Server 2008，对于 2005，更改 INSERT INTO @tbl ... 语法。

Answer 2

你可以这样说：

WHERE NOT EXISTS (SELECT * FROM conn_cte AS parents WHERE t.path LIKE parents.path + '%')