SQL-запрос CTE получает полные пути

У меня есть простой запрос

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


sql-server common-table-expression hierarchy
person marcinn    schedule 04.01.2010    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Вы могли бы сказать что-то вроде:

WHERE NOT EXISTS (SELECT * FROM conn_cte AS parents WHERE t.path LIKE parents.path + '%')
person Rob Farley    schedule 04.01.2010
comment
Но имейте в виду, что если у вас есть 0,8,12,16,17,183 - это может вас запутать. Вы можете поставить запятую раньше, чтобы ваш путь выглядел как 0,8,12,16,17,18, - person Rob Farley; 05.01.2010

arrow_upward
3
arrow_downward 

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 ....

person Damir Sudarevic    schedule 05.01.2010