使用SQL Server获取层次结构 [英] Get hierarchical structure using SQL Server

问题描述

我有一个带有主键id和外键parent_id的自引用表.

I have a self-referencing table with a primary key, id and a foreign key parent_id.

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PK  | NULL    | IDENTITY       | 
| parent_id  | int(11)      | YES  |     | NULL    |                | 
| name       | varchar(255) | YES  |     | NULL    |                | 
+------------+--------------+------+-----+---------+----------------+

我有一张下表(减少数据以便更清楚)

I have got a table as following (reduce data for more clear)

表MySiteMap

  Id   Name  parent_id
   1    A    NULL
   2    B    1
   3    C    1
   4    D    1

  20    B1   2  
  21    B2   2

  30    C1   3
  31    C2   3

  40    D1   4
  41    D2   4

我想使用SQL Server查询获得层次结构:

I would like get the hierarchical structure using SQL Server query:

  A 
  |
   B
    |
    |  B1
    |  B2
   C
    |
    |  C1
    |  C2
   D
    |
    |  D1
    |  D2

有什么建议吗?

推荐答案

您可以使用公用表表达式.

WITH LeveledSiteMap(Id, Name, Level)
AS
(
    SELECT Id, Name, 1 AS Level
    FROM MySiteMap
    WHERE Parent_Id IS NULL
    UNION ALL
    SELECT m.Id, m.Name, l.Level + 1
    FROM MySiteMap AS m
        INNER JOIN LeveledSiteMap AS l
        ON m.Parent_Id = l.Id
)
SELECT *
FROM LeveledSiteMap

这篇关于使用SQL Server获取层次结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！