主页 / dba / 问题 / 160924
Accepted
McNets
Asked: 2017-01-13 13:59:34 +0800 CST

我可以从自引用（分层）表中获取树结构吗？

  • 772

给定这样的分层表：

CREATE TABLE [dbo].[btree]
(
  id INT PRIMARY KEY
, parent_id INT REFERENCES [dbo].[btree] ([id])
, name NVARCHAR(20)
);

我想获得整个树结构。

例如，使用此数据：

INSERT INTO [btree] VALUES (1, null, '1 Root');
INSERT INTO [btree] VALUES (2,    1, '1.1 Group');
INSERT INTO [btree] VALUES (3,    1, '1.2 Group');
INSERT INTO [btree] VALUES (4,    2, '1.1.1 Group');
INSERT INTO [btree] VALUES (5,    2, '1.1.2 Group');
INSERT INTO [btree] VALUES (6,    3, '1.2.1 Group');
INSERT INTO [btree] VALUES (7,    3, '1.2.2 Group');
INSERT INTO [btree] VALUES (8,    4, '1.1.1.1 Items');
INSERT INTO [btree] VALUES (9,    4, '1.1.1.2 Items');
INSERT INTO [btree] VALUES (10,   5, '1.1.2.1 Items');
INSERT INTO [btree] VALUES (11,   5, '1.1.1.2 Items');
INSERT INTO [btree] VALUES (12,   6, '1.2.1.1 Items');
INSERT INTO [btree] VALUES (13,   6, '1.2.1.2 Items');
INSERT INTO [btree] VALUES (14,   7, '1.2.2.1 Items');

我想获得：

+----+-----------+---------------------+
| id | parent_id | description         |
+----+-----------+---------------------+
|  1 |    NULL   | 1 Root              |
|  2 |     1     |   1.1 Group         |
|  4 |     2     |     1.1.1 Group     |
|  8 |     4     |       1.1.1.1 Items |
|  9 |     4     |       1.1.1.2 Items |
|  5 |     2     |     1.1.2 Group     |
| 10 |     5     |       1.1.2.1 Items |
| 11 |     5     |       1.1.2.2 Items |
|  3 |     1     |   1.2 Group         |
|  6 |     3     |     1.2.1 Group     |
| 12 |     6     |       1.2.1.1 Items |
| 13 |     6     |       1.2.1.2 Items |
|  7 |     3     |     1.2.2 Group     |
| 14 |     7     |       1.2.2.1 Items |
+----+-----------+---------------------+

我正在使用这样的递归查询来获取记录：

;WITH tree AS
(
    SELECT c1.id, c1.parent_id, c1.name, [level] = 1
    FROM dbo.[btree] c1
    WHERE c1.parent_id IS NULL
    UNION ALL
    SELECT c2.id, c2.parent_id, c2.name, [level] = tree.[level] + 1
    FROM dbo.[btree] c2 INNER JOIN tree ON tree.id = c2.parent_id
)
SELECT tree.level, tree.id, parent_id, REPLICATE('  ', tree.level - 1) + tree.name AS description
FROM tree
OPTION (MAXRECURSION 0)
;

这是当前的结果：

+----+-----------+---------------------+
| id | parent_id | description         |
|  1 |    NULL   | 1 Root              |
|  2 |     1     |   1.1 Group         |
|  3 |     1     |   1.2 Group         |
|  6 |     3     |     1.2.1 Group     |
|  7 |     3     |     1.2.2 Group     |
| 14 |     7     |       1.2.2.1 Items |
| 12 |     6     |       1.2.1.1 Items |
| 13 |     6     |       1.2.1.2 Items |
|  4 |     2     |     1.1.1 Group     |
|  5 |     2     |     1.1.2 Group     |
| 10 |     5     |       1.1.2.1 Items |
| 11 |     5     |       1.1.1.2 Items |
|  8 |     4     |       1.1.1.1 Items |
|  9 |     4     |       1.1.1.2 Items |
+----+-----------+---------------------+

我不知道如何按级别排序。

有没有办法为每个子级别设置排名？

我设置了一个Rextester

t-sql sql-server-2014

2 个回答

  1. Best Answer

    添加“路径”字段并按类似于文件路径的方式排序。正如 ypercube 所提到的，在这个例子中排序过于简单，只是碰巧可以工作，但为了简单起见，我将保持原样。大多数时候，当我使用这种模式时，我还是按名称而不是 ID 进行排序。

    IF OBJECT_ID('[dbo].[btree]', 'U') IS NOT NULL 
    DROP TABLE [dbo].[btree];
GO

CREATE TABLE [dbo].[btree]
(
  id INT PRIMARY KEY
, parent_id INT REFERENCES [dbo].[btree] ([id])
, name NVARCHAR(20)
);
GO

INSERT INTO [btree] VALUES (1, null, '1 Root');
INSERT INTO [btree] VALUES (2,    1, '1.1 Group');
INSERT INTO [btree] VALUES (3,    1, '1.2 Group');
INSERT INTO [btree] VALUES (4,    2, '1.1.1 Group');
INSERT INTO [btree] VALUES (5,    2, '1.1.2 Group');
INSERT INTO [btree] VALUES (6,    3, '1.2.1 Group');
INSERT INTO [btree] VALUES (7,    3, '1.2.2 Group');
INSERT INTO [btree] VALUES (8,    4, '1.1.1.1 Items');
INSERT INTO [btree] VALUES (9,    4, '1.1.1.2 Items');
INSERT INTO [btree] VALUES (10,   5, '1.1.2.1 Items');
INSERT INTO [btree] VALUES (11,   5, '1.1.2.2 Items');
INSERT INTO [btree] VALUES (12,   6, '1.2.1.1 Items');
INSERT INTO [btree] VALUES (13,   6, '1.2.1.2 Items');
INSERT INTO [btree] VALUES (14,   7, '1.2.2.1 Items');

;WITH tree AS
(
    SELECT c1.id, c1.parent_id, c1.name, [level] = 1, path = cast('root' as varchar(100))
    FROM dbo.[btree] c1
    WHERE c1.parent_id IS NULL
    UNION ALL
    SELECT c2.id, c2.parent_id, c2.name, [level] = tree.[level] + 1, 
           Path = Cast(tree.path+'/'+right('000000000' + cast(c2.id as varchar(10)),10) as varchar(100))
    FROM dbo.[btree] c2 INNER JOIN tree ON tree.id = c2.parent_id
)
SELECT tree.path, tree.id, parent_id, REPLICATE('  ', tree.level - 1) + tree.name AS description
FROM tree
Order by path
OPTION (MAXRECURSION 0)
;

    这里有一个rextester

    • 7

  2. 作弊，只是一点点;）看，没有递归！

    在rextester.com测试

    SELECT btree.*        -- , a,b,c,d     -- uncomment to see the parts
FROM btree 
  OUTER APPLY
    ( SELECT rlc = REVERSE(LEFT(name, CHARINDEX(' ', name)-1))) AS r
  OUTER APPLY
    ( SELECT a = CAST(REVERSE(PARSENAME(r.rlc, 1)) AS int),
             b = CAST(REVERSE(PARSENAME(r.rlc, 2)) AS int),
             c = CAST(REVERSE(PARSENAME(r.rlc, 3)) AS int),
             d = CAST(REVERSE(PARSENAME(r.rlc, 4)) AS int)
    ) AS p 
ORDER BY a, b, c, d ;

    当然，上述内容相当有限。它仅在以下假设下有效：

    • 该name列已存储（在第一部分中）实际的“路径”。
    • 树的深度最大为 4（因此路径最多有 4 个部分）。
    • CAST .. AS int仅当零件是数字时才需要。

    说明：代码使用函数工作，该函数PARSENAME()的主要目的是将对象名称拆分为 4 个部分：

    Server.Database.Schema.Object
  |        |       |      |
 4th      3rd     2nd    1st

    请注意，顺序是相反的。作为一个例子，PARSENAME('dbo.btree', 2)会给我们'dbo'一个结果。使用 3，我们将得到 NULL（这就是为什么REVERSE()在代码中使用了两次。否则我们会在开始时得到空值。当我们想要时，'1.2'将被解析为。 )null, null, 1, 21, 2, null, null

    结论：毕竟，我应该补充一点，鲍勃坎贝尔的答案是要走的路，因为它更通用，并产生（在结果的“路径”列中）路径层次结构，然后可以用于ORDER BY.

    您可能会考虑的其他选项 - 如果表的大小变大并且递归解决方案变慢 - 是将路径实际存储在单独的列中（以有利于排序的格式，即使用填充）或使用提供的HierarchyID正是这种用例的类型，分层数据。

    • 4

相关问题