树表的Sql查询 [英] Sql query for tree table

问题描述

我有一个树状结构的表:

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

在 sql 查询结果中，我需要一个表，如:

In sql query resut I need a table like:

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

谁能帮我写ms-sql查询?谢谢！

Who can help me to write ms-sql query? Thanks!

推荐答案

扩展 a_horse_with_no_name 的答案，这展示了如何使用 SQL Server 的 实现递归 CTE(递归单记录交叉应用)与 row_number() 结合以产生问题中的确切输出.

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.

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 cast('/' + RN + '/' as hierarchyid)

老实说，使用 ID 本身来生成树路径"会起作用，因为我们直接按 id 订购，但我想我会插入 row_number() 函数.

这篇关于树表的Sql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！