SQL 树表的Sql查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5522478/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sql query for tree table
提问by ihorko
I have a table with tree structure:
我有一个树结构的表：
id parentId name
----------------
1 0 Category1
2 0 Category2
3 1 Category3
4 2 Category4
5 1 Category5
6 2 Category6
7 3 Category7
In sql query resut I need a table like:
在 sql 查询结果中，我需要一个表，如：
id parentId level name
----------------------
1 0 0 Category1
3 1 1 Category3
7 3 2 Category7
5 1 1 Category5
2 0 0 Category2
4 2 1 Category4
6 2 1 Category6
Who can help me to write ms-sql query? Thanks!
谁能帮我写ms-sql查询？谢谢！
回答by RichardTheKiwi
Expanding on a_horse_with_no_name's answer, this show how to use SQL Server's implementation of recursive CTE(recursive single-record cross apply) in combination with row_number() to produce the exact output in the question.
扩展 a_horse_with_no_name 的答案，这显示了如何将 SQL Server 的递归 CTE（递归单记录交叉应用）的实现与 row_number() 结合使用以生成问题中的确切输出。
declare @t table(id int,parentId int,name varchar(20))
insert @t select 1, 0 ,'Category1'
insert @t select 2, 0, 'Category2'
insert @t select 3, 1, 'Category3'
insert @t select 4 , 2, 'Category4'
insert @t select 5 , 1, 'Category5'
insert @t select 6 , 2, 'Category6'
insert @t select 7 , 3, 'Category7'
;
WITH tree (id, parentid, level, name, rn) as
(
SELECT id, parentid, 0 as level, name,
convert(varchar(max),right(row_number() over (order by id),10)) rn
FROM @t
WHERE parentid = 0
UNION ALL
SELECT c2.id, c2.parentid, tree.level + 1, c2.name,
rn + '/' + convert(varchar(max),right(row_number() over (order by tree.id),10))
FROM @t c2
INNER JOIN tree ON tree.id = c2.parentid
)
SELECT *
FROM tree
order by RN
To be honest, using the IDs themselves to produce the tree "path" would work, since we are ordering directly by id, but I thought I'd slip in the row_number() function.
老实说，使用 ID 本身来生成树“路径”是可行的，因为我们直接按 id 排序，但我想我会插入 row_number() 函数。
回答by a_horse_with_no_name
WITH tree (id, parentid, level, name) as
(
SELECT id, parentid, 0 as level, name
FROM your_table
WHERE parentid = 0
UNION ALL
SELECT c2.id, c2.parentid, tree.level + 1, c2.name
FROM your_table c2
INNER JOIN tree ON tree.id = c2.parentid
)
SELECT *
FROM tree;
I have currently no SQL Server at hand to test it, so there might be some typos (syntax errors) in there
我目前手头没有 SQL Server 来测试它，所以那里可能有一些拼写错误（语法错误）
