名称上具有order by的父子层次结构

SQL Server中几乎所有层次结构问题都是通过递归cte解决的。 您没有提到是否可以有多个层次结构级别,但由于没有办法阻止另一个级别,然后编写一个instead of触发器或基于udf的检查约束,因此我假设可以有多个层次结构行。
这个答案的诀窍在于,按字母顺序排序的数字与按数字排序的数字不同。
如果您排序 1, 11, 2, 13, 21, 3 在一个 数字 分类
你会得到 1, 2, 3, 11, 13, 21 .
但是,在 按字母顺序排列的 分类
你会得到 1, 11, 13, 2, 21, 3 .

现在,讨论够了,让我们看看一些代码!
首先,创建并填充示例表( 请 请在您将来的问题中为我们保存此步骤):

DECLARE @T AS TABLE
(
    Id int,
    [Name] char(3),
    parentId int
)
INSERT INTO @T (Id, [Name], parentId) VALUES
(1, 'STU', 0),
(2, 'XYZ', 5),
(3, 'PQR', 5),
(4, 'EFG', 0),
(5, 'ABC', 0),
(6, 'HIJ', 1),
(7, 'DEF', 1),
(8, 'AAA', 3),
(9, 'ZZZ', 3)

笔记 :我又为孙子添加了两行以检查多级层次结构。

cte:

;WITH CTE AS
(
    SELECT  Id, 
            [Name], 
            ParentId, 
            -- Row_Number returns a bigint - max value have 19 digits
            CAST(ROW_NUMBER() OVER(ORDER BY [Name]) as varchar(19)) As Sort
    FROM @T 
    WHERE parentId = 0

    UNION ALL

    SELECT  T.Id, 
            T.[Name], 
            T.ParentId,
            CAST(Sort + CAST(ROW_NUMBER() OVER(ORDER BY T.[Name]) as varchar(19)) as varchar(19))
    FROM @T T
    JOIN CTE ON T.parentId = CTE.Id 
)

查询:

SELECT Id, [Name], ParentId
FROM CTE 
ORDER BY Sort -- alphabetic sort will order 11 before 2...

结果:

Id  Name    ParentId
5   ABC     0
3   PQR     5
8   AAA     3
9   ZZZ     3
2   XYZ     5
4   EFG     0
1   STU     0
7   DEF     1
6   HIJ     1

这相当直截了当,真的:

SELECT
    category.*,
    -- bring parent and its children together
    CASE WHEN parent.Id IS NULL THEN category.Name ELSE parent.Name END AS sort1,
    -- move parent to top followed by its children
    CASE WHEN parent.Id IS NULL THEN NULL ELSE category.Name END AS sort2
FROM category
LEFT JOIN category AS parent ON category.parentId = parent.Id
ORDER BY sort1, sort2

输出:

+------+------+----------+-------+-------+
| Id   | Name | parentId | sort1 | sort2 |
+------+------+----------+-------+-------+
|    5 | ABC  |        0 | ABC   | NULL  |
|    3 | PQR  |        5 | ABC   | PQR   |
|    2 | XYZ  |        5 | ABC   | XYZ   |
|    4 | EFG  |        0 | EFG   | NULL  |
|    1 | STU  |        0 | STU   | NULL  |
|    7 | DEF  |        1 | STU   | DEF   |
|    6 | HIJ  |        1 | STU   | HIJ   |
+------+------+----------+-------+-------+

请注意,我已将排序计算放在 SELECT 子句,以解释其工作原理。

试试这个

;WITH CTE
AS
(
   SELECT
  RN =0,   
  ID,
      NAME,
     parentId = ID
  FROM T1
    WHERE parentId = 0

  UNION ALL

  SELECT
  RN = ROW_NUMBER() OVER(PARTITION BY T1.parentId ORDER BY T1.name asc),
     T1.ID,
      T1.NAME,
  T1.parentId
  FROM T1
   INNER JOIN T1 T2
      ON T1.parentId =T2.ID

)
SELECT
id,
name,
parentid
FROM CTE
ORDER BY parentId DESC,RN

如果您不知道子深度,我通常使用动态Sql来解决这个问题。但由于您似乎只有2级的深度,因此这可能会起作用:

declare @tempT table(ID int, name varchar(3), parentID int, sortLevel1 int, sortlevel2 int)

insert into @tempT
select t1.ID,t1.name,t1.parentID,RowNumber() Over(order by (select null)),-1
from table t1  
where parentId=0
order by t1.name 

insert into @tempT
select t2.ID,t2.name,t2.parentID,t1.sortLevel1,RowNumber() Over(order by (select null))
from table t1  
join @tempT t2 on t1.id=t2.parentID
order by t2.name

select * from @temp order by t1.sortLevel1, sortLevel2

经过一些调试,我发现 Zohar Peled 如果某个层次结构级别的;9个条目。我通过修改他的代码解决了这个问题,他在代码中通过添加前导零来构造一个“排序”列。

;WITH CTE AS
(
    SELECT  Id, 
            [Name], 
            ParentId, 
            -- Row_Number returns a bigint - max value have 19 digits
            CAST(FORMAT(ROW_NUMBER() OVER(ORDER BY [Name]), 'D4') as varchar(12)) As Sort
    FROM @T 
    WHERE parentId = 0

    UNION ALL

    SELECT  T.Id, 
            T.[Name], 
            T.ParentId,
            CAST(Sort + CAST(FORMAT(ROW_NUMBER() OVER(ORDER BY [Name]), 'D4') as varchar(4)) as varchar(12))
    FROM @T T
    JOIN CTE ON T.parentId = CTE.Id 
)

在我的情况下,我不需要超过4个字符。但如果需要更多,只需自己添加,不要忘记根据层次结构中的级别数乘以所需字符长度来更改排序列的长度。