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

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

 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

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

网上有很多有用的文章。

有用的链接

简单讲：SQL Server CTE 基础知识 https://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/

blog.sqlauthority：递归 CTE http://blog.sqlauthority.com/2008/07/28/sql-server-simple-example-of-recursive-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;