sql在一个字段中选择父级子级递归

Question

我不知道如何选择查询递归。

 id     idparent    jobNO
--------------------------------
  1         0         1
  2         1         2
  3         1         3
  4         0         4
  5         4         5
  6         4         6

如何使用SqlServer取得这样的结果

 id     idparent    jobNO   ListJob
----------------------------------------
  1         0         1        1
  2         1         2        1/2
  3         1         3        1/3
  4         0         4        4
  5         4         5        4/5
  6         5         6        4/5/6

Answer 1

您需要使用递归公用表表达式。

在线上有很多有用的文章。

有用的链接

简单谈谈：SQL Server CTE基础

blog.sqlauthority：递归CTE

这是您的问题的解决方案：

   CREATE TABLE #TEST
    (
        id int not null,
        idparent int not null,
        jobno int not null
    );

    INSERT INTO #Test VALUES 
    (1,0,1),
    (2,1,2),
    (3,1,3),
    (4,0,4),
    (5,4,5),
    (6,5,6);

    WITH CTE AS (
-- This is end of the recursion: Select items with no parent
        SELECT id, idparent, jobno, CONVERT(VARCHAR(MAX),jobno) AS ListJob
        FROM #Test
        WHERE idParent = 0
    UNION ALL
-- This is the recursive part: It joins to CTE
        SELECT t.id, t.idparent, t.jobno,  c.ListJob + '/' + CONVERT(VARCHAR(MAX),t.jobno) AS ListJob
        FROM #Test t
        INNER JOIN CTE c ON t.idParent = c.id
    )
    SELECT * FROM CTE
    ORDER BY id;