sql - 如何使用子查询选择路径？

Question

我需要为每一行设置路径。
用父、子和路径创建表

CREATE TABLE CTE(
    id int,
    Title VARCHAR(20),
    ParentTitle VARCHAR(20),
    Path VARCHAR(20),    
)   

INSERT INTO CTE
VALUES
(1,'p1', 'Home', ''),
(2,'p1.1', 'p1', ''),
(3,'p1.1.1', 'p1', ''),    
(4,'p2', 'Home', ''), 
(5,'p2.1', 'p2', ''), 
(6,'p2.2', 'p2', ''),    
(7,'p2.3', 'p2.2', ''),
(8,'p3', 'Home', ''), 
(9,'p3.1', 'p3', ''), 
(10,'P2.2', 'p3.1', '')

我需要如下表的输出

╔════╦════════╦═════════════╦═══════════════════════╗
║ id ║ Title  ║ ParentTitle ║ Path                  ║
╠════╬════════╬═════════════╬═══════════════════════╣
║  1 ║ p1     ║ Home        ║ (home\p1)             ║
║  2 ║ p1.1   ║ p1          ║ (home\p1\p1.1)        ║
║  3 ║ p1.1.1 ║ p1          ║ (home\p1\p1.1\p1.1.1) ║
║  4 ║ p2     ║ Home        ║ (home\p2)             ║
║  5 ║ p2.1   ║ p2          ║ (home\p2\p2.1)        ║
║  6 ║ p2.2   ║ p2          ║ (home\p2\p2.2)        ║
║  7 ║ p2.2   ║ p2.2        ║ (home\p2\p2.2\2.3)    ║
║  8 ║ P3     ║ Home        ║ (home\p3)             ║
║  9 ║ p3.1   ║ p3          ║ (home\p3\p3.1)        ║
║ 10 ║ P2.2   ║ p3.1        ║ (home\p3\p3.1\p2.2)   ║
╚════╩════════╩═════════════╩═══════════════════════╝

我试过这个查询，但我无法得到正确的解决方案。

SELECT title,
       (SELECT CASE
                 WHEN c2.title != '' THEN c1.title + '\' + c2.title
               END
        FROM   cte C1
               LEFT JOIN cte C2
                      ON c1.title = c2.parenttitle
        WHERE  C.id = c1.id) AS root
FROM   cte C

Answer 1

我认为您可以使用这样的查询:

;WITH CTE AS (
    SELECT id, Title, ParentTitle, CAST('home\'+ Title as varchar(255)) As [path]
    FROM yourTable
    WHERE ParentTitle = 'Home'
    UNION ALL
    SELECT yourTable.id, yourTable.Title, yourTable.ParentTitle, CAST(CTE.[path] + '\' + yourTable.Title As varchar(255))
    FROM yourTable JOIN CTE
    ON CTE.Title = yourTable.ParentTitle AND yourTable.ParentTitle != 'Home'
)
SELECT *
FROM CTE
ORDER BY id;

[SQL Fiddle Demo]

另一种方法是使用 UNION ALL与 JOIN每个级别的 s 像这样:

SELECT t.id, t.Title, t.ParentTitle, 'home\' + t.Title [path]
FROM yourTable t
WHERE t.ParentTitle = 'Home'
UNION ALL
SELECT t1.id, t1.Title, t1.ParentTitle, 'home\' + t.Title +'\' + t1.Title
FROM yourTable t JOIN yourTable t1 ON t.Title = t1.ParentTitle
WHERE t.ParentTitle = 'Home'
UNION ALL
SELECT t2.id, t2.Title, t2.ParentTitle, 'home\' + t.Title +'\' + t1.Title + '\' + t2.Title
FROM yourTable t JOIN yourTable t1 ON t.Title = t1.ParentTitle JOIN yourTable t2 ON t1.Title = t2.ParentTitle
WHERE t.ParentTitle = 'Home';