logo标签列表

使用SQL Server获取层级结构

sqlsql-serverhierarchical-datarecursive-query
4

我有一个自引用的表，有一个主键id和一个外键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    |                | 
+------------+--------------+------+-----+---------+----------------+

我有一个表格如下所示（为了更清晰，减少了数据）

表格 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 查询获得分层结构：
  A 
  |
   B
    |
    |  B1
    |  B2
   C
    |
    |  C1
    |  C2
   D
    |
    |  D1
    |  D2

有什么建议吗？
4个回答
6

你可以使用公用表达式。

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
2
请使用以下内容：
;WITH CTE(Id, Name, parent_id, [Level], ord) AS (
    SELECT 
        MySiteMap.Id, 
        CONVERT(nvarchar(255), MySiteMap.Name) AS Name, 
        MySiteMap.parent_id, 
        1,
        CONVERT(nvarchar(255), MySiteMap.Id) AS ord
    FROM MySiteMap
    WHERE MySiteMap.parent_id IS NULL
    UNION ALL
    SELECT 
        MySiteMap.Id, 
        CONVERT(nvarchar(255), REPLICATE(' ', [Level]) + '|' + REPLICATE(' ', [Level]) + MySiteMap.Name) AS Name, 
        MySiteMap.parent_id, 
        CTE.[Level] + 1,
        CONVERT(nvarchar(255),CTE.ord + CONVERT(nvarchar(255), MySiteMap.Id)) AS ord
    FROM MySiteMap
        JOIN CTE ON MySiteMap.parent_id =CTE.Id
    WHERE MySiteMap.parent_id IS NOT NULL
)
SELECT Name
FROM CTE
ORDER BY ord

对于这个问题：

A
 | B
  |  B1
  |  B2
 | C
  |  C1
  |  C2
 | D
  |  D1
  |  D2
1
我从一个查询开始（但现在看起来与Mark的相似）。无论如何，我会添加它，同时我还创建了一个sqlfiddle，其中包括我的和Mark的查询。
WITH tList (id,name,parent_id,nameLevel)
AS
(
    SELECT t.id, t.name, t.parent_id, 1 AS nameLevel
    FROM t as t
    WHERE t.parent_id IS NULL
    UNION ALL
    SELECT tnext.id, tnext.name, tnext.parent_id, tList.nameLevel + 1
    FROM t AS tnext
    INNER JOIN tList AS tlist
        ON tnext.parent_id = tlist.id
)
SELECT id,name,isnull(parent_id,0) 'parent_id',nameLevel FROM tList order by nameLevel;

一个好的博客： SQL查询 - 如何以分层结构获取数据？
0
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接