sql - 按名称对嵌套集进行排序，同时保持深度完整性

Question

我正在使用稍后将用于为我的网站构建站点地图的嵌套集模型。这是我的表结构。

create table departments (
    id int identity(0, 1) primary key
    , lft int
    , rgt int
    , name nvarchar(60)
);

insert into departments (lft, rgt, name) values (1, 10, 'departments');
insert into departments (lft, rgt, name) values (2, 3, 'd');
insert into departments (lft, rgt, name) values (4, 9, 'a');
insert into departments (lft, rgt, name) values (5, 6, 'b');
insert into departments (lft, rgt, name) values (7, 8, 'c');

如何按深度和名称排序？我能行

select
    replicate('----', count(parent.name) - 1) + ' ' + node.name
    , count(parent.name) - 1 as depth
, node.lft
from
    departments node
    , departments parent
where
    node.lft between parent.lft and parent.rgt
group by
    node.name, node.lft
order by
    depth asc, node.name asc;

但是，出于某种原因，这并不匹配 child 与他们的 parent 。

department      lft     rgt
---------------------------
 departments    0       1
---- a        1        4
---- d        1        2
-------- b    2        5
-------- c    2        7

如您所见，“d”部门有“a”部门的 child !

谢谢。

Answer 1

我想我终于想出了一个 ANSI SQL 解决方案。基本要点是它计算行数，这些行要么具有与节点自己的父节点之一相同级别的具有较低值名称的父节点，要么本身与节点处于同一级别且具有较低值名称的行数。如果需要，您需要对其进行细微调整才能添加缩进。另外，我不知道由于所有子查询，大型数据集的性能如何:

SELECT
    N1.name
FROM
    dbo.departments N1
ORDER BY
    (
    SELECT
        COUNT(DISTINCT N2.lft)
    FROM
        dbo.departments N2
    INNER JOIN (
                SELECT
                    N.name,
                    N.lft,
                    N.rgt,
                    (SELECT COUNT(*) FROM dbo.departments WHERE lft < N.lft AND rgt > N.lft) AS depth
                FROM
                    dbo.departments N) SQ1 ON
        SQ1.lft <= N2.lft AND SQ1.rgt >= N2.lft
    INNER JOIN (
                SELECT
                    N3.name,
                    N3.lft,
                    N3.rgt,
                    (SELECT COUNT(*) FROM dbo.departments WHERE lft < N3.lft AND rgt > N3.lft) AS depth
                FROM
                    dbo.departments N3) SQ2 ON
        SQ2.lft <= N1.lft AND SQ2.rgt >= N1.lft AND
        SQ2.depth = SQ1.depth AND
        SQ2.name > SQ1.name
    )

如果您遇到任何它中断的情况，请告诉我。