sql在一个字段中选择父级子级递归 [英] sql select parent child recursive in one field
本文介绍了sql在一个字段中选择父级子级递归的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我不知道如何选择查询递归.
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.
有用的链接
以下是您的问题的解决方案:
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屋!
查看全文