sql在一个字段中选择父级子级递归 [英] sql select parent child recursive in one field

问题描述

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

I do not know how to select query recursive..

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

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

how do the results like this With 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

推荐答案

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

You need to use a Recursive Common Table Expression.

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

There are many useful articles online.

有用的链接

简单讨论:SQL Server CTE基础

blog.sqlauthority:递归CTE

以下是您的问题的解决方案:

Here is a solution to your question:

   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;

这篇关于sql在一个字段中选择父级子级递归的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！