sql - 在 SQL Server 中排序递归结果集

Question

我在构建返回 XML 样式层次结构的查询时遇到了极大的困难。

我们有一个数据库表，其中包含我们网站的 URL 层次结构。该表包含以下列:ID、URL、DisplayName、ParentID、ItemOrder

父 ID 在当前项与其父项之间形成递归关系。该项目在层次结构中应位于其父级下方，并且还应使用针对层次结构中同一级别的项目的项目顺序对其进行排序。

我已经设法使递归查询正常工作，因此它按顺序向下钻取层次结构，但我也无法按项目顺序对其进行排序。

我当前的查询如下:

WITH Parents AS
(
SELECT MenuItemId, URL, ParentItemId, ItemOrder
FROM CambsMenu

UNION ALL

SELECT si.MenuItemId, si.URL, si.ParentItemId, si.ItemOrder
FROM CambsMenu si INNER JOIN Parents p
ON si.ParentItemId = p.MenuItemId
)

SELECT DISTINCT *
FROM Parents

Answer 1

普通的分层方法:

select *
into emp
from 
(values
(1, 'President', NULL),
(2, 'Vice President', 1),
(3, 'CEO', 2),
(4, 'CTO', 2),
(5, 'Group Project Manager', 4),
(6, 'Project Manager 1', 5),
(7, 'Project Manager 2', 5),
(8, 'Team Leader 1', 6),
(9, 'Software Engineer 1', 8),
(10, 'Software Engineer 2', 8),
(11, 'Test Lead 1', 6),
(12, 'Tester 1', 11),
(13, 'Tester 2', 11),
(14, 'Team Leader 2', 7),
(15, 'Software Engineer 3', 14),
(16, 'Software Engineer 4', 14),
(17, 'Test Lead 2', 7),
(18, 'Tester 3', 17),
(19, 'Tester 4', 17),
(20, 'Tester 5', 17)
) as x(emp_id, emp_name, mgr_id)

查询:

with recursive org(emp_id, emp_name, emp_level, mgr_id, sort) as
(
 select  
  a.emp_id, a.emp_name, 0, a.mgr_id,  
  a.emp_name
 from emp a
 where a.mgr_id is null

 union all

 select 
  b.emp_id, b.emp_name, emp_level + 1, b.mgr_id, 

  sort || ' : ' || b.emp_name 

 from emp b
 join org on org.emp_id = b.mgr_id
)
select 
 emp_id, repeat(' ', emp_level * 2) || emp_name as emp_name, sort 
from org
order by sort

输出:

 emp_id |            emp_name             |                                                        sort                                                        
--------+---------------------------------+--------------------------------------------------------------------------------------------------------------------
      1 | President                       | President
      2 |   Vice President                | President : Vice President
      3 |     CEO                         | President : Vice President : CEO
      4 |     CTO                         | President : Vice President : CTO
      5 |       Group Project Manager     | President : Vice President : CTO : Group Project Manager
      6 |         Project Manager 1       | President : Vice President : CTO : Group Project Manager : Project Manager 1
      8 |           Team Leader 1         | President : Vice President : CTO : Group Project Manager : Project Manager 1 : Team Leader 1
      9 |             Software Engineer 1 | President : Vice President : CTO : Group Project Manager : Project Manager 1 : Team Leader 1 : Software Engineer 1
     10 |             Software Engineer 2 | President : Vice President : CTO : Group Project Manager : Project Manager 1 : Team Leader 1 : Software Engineer 2
     11 |           Test Lead 1           | President : Vice President : CTO : Group Project Manager : Project Manager 1 : Test Lead 1
     12 |             Tester 1            | President : Vice President : CTO : Group Project Manager : Project Manager 1 : Test Lead 1 : Tester 1
     13 |             Tester 2            | President : Vice President : CTO : Group Project Manager : Project Manager 1 : Test Lead 1 : Tester 2
      7 |         Project Manager 2       | President : Vice President : CTO : Group Project Manager : Project Manager 2
     14 |           Team Leader 2         | President : Vice President : CTO : Group Project Manager : Project Manager 2 : Team Leader 2
     15 |             Software Engineer 3 | President : Vice President : CTO : Group Project Manager : Project Manager 2 : Team Leader 2 : Software Engineer 3
     16 |             Software Engineer 4 | President : Vice President : CTO : Group Project Manager : Project Manager 2 : Team Leader 2 : Software Engineer 4
     17 |           Test Lead 2           | President : Vice President : CTO : Group Project Manager : Project Manager 2 : Test Lead 2
     18 |             Tester 3            | President : Vice President : CTO : Group Project Manager : Project Manager 2 : Test Lead 2 : Tester 3
     19 |             Tester 4            | President : Vice President : CTO : Group Project Manager : Project Manager 2 : Test Lead 2 : Tester 4
     20 |             Tester 5            | President : Vice President : CTO : Group Project Manager : Project Manager 2 : Test Lead 2 : Tester 5
(20 rows)

现在让我们覆盖组项目经理的排序，让项目经理 2 在 1 之前，项目经理 1 在项目经理 2 之后。我们也让测试人员 4 在 3 之前，测试人员 3 在测试人员 4 之后

alter table emp add column order_override int null;

update emp set order_override = 1 where emp_id = 7; -- PM 2
update emp set order_override = 2 where emp_id = 6; -- PM 1

update emp set order_override = 1 where emp_id = 19; -- Tester 4
update emp set order_override = 2 where emp_id = 18; -- Tester 3

查询:

with recursive org(emp_id, emp_name, emp_level, mgr_id, sort) as
(
 select  
  a.emp_id, a.emp_name, 0, a.mgr_id,  
  a.emp_name
 from emp a
 where a.mgr_id is null

 union all

 select 
  b.emp_id, b.emp_name, emp_level + 1, b.mgr_id, 

  sort || ' : ' || coalesce( lpad(order_override::text, 10, '0'), b.emp_name )
 from emp b
 join org on org.emp_id = b.mgr_id
)
select 
 emp_id, repeat(' ', emp_level * 2) || emp_name as emp_name, sort 
from org
order by sort

输出:

 emp_id |            emp_name             |                                                    sort                                                     
--------+---------------------------------+-------------------------------------------------------------------------------------------------------------
      1 | President                       | President
      2 |   Vice President                | President : Vice President
      3 |     CEO                         | President : Vice President : CEO
      4 |     CTO                         | President : Vice President : CTO
      5 |       Group Project Manager     | President : Vice President : CTO : Group Project Manager
      7 |         Project Manager 2       | President : Vice President : CTO : Group Project Manager : 0000000001
     14 |           Team Leader 2         | President : Vice President : CTO : Group Project Manager : 0000000001 : Team Leader 2
     15 |             Software Engineer 3 | President : Vice President : CTO : Group Project Manager : 0000000001 : Team Leader 2 : Software Engineer 3
     16 |             Software Engineer 4 | President : Vice President : CTO : Group Project Manager : 0000000001 : Team Leader 2 : Software Engineer 4
     17 |           Test Lead 2           | President : Vice President : CTO : Group Project Manager : 0000000001 : Test Lead 2
     19 |             Tester 4            | President : Vice President : CTO : Group Project Manager : 0000000001 : Test Lead 2 : 0000000001
     18 |             Tester 3            | President : Vice President : CTO : Group Project Manager : 0000000001 : Test Lead 2 : 0000000002
     20 |             Tester 5            | President : Vice President : CTO : Group Project Manager : 0000000001 : Test Lead 2 : Tester 5
      6 |         Project Manager 1       | President : Vice President : CTO : Group Project Manager : 0000000002
      8 |           Team Leader 1         | President : Vice President : CTO : Group Project Manager : 0000000002 : Team Leader 1
      9 |             Software Engineer 1 | President : Vice President : CTO : Group Project Manager : 0000000002 : Team Leader 1 : Software Engineer 1
     10 |             Software Engineer 2 | President : Vice President : CTO : Group Project Manager : 0000000002 : Team Leader 1 : Software Engineer 2
     11 |           Test Lead 1           | President : Vice President : CTO : Group Project Manager : 0000000002 : Test Lead 1
     12 |             Tester 1            | President : Vice President : CTO : Group Project Manager : 0000000002 : Test Lead 1 : Tester 1
     13 |             Tester 2            | President : Vice President : CTO : Group Project Manager : 0000000002 : Test Lead 1 : Tester 2
(20 rows)

数据投影中没有排序列:

with recursive org(emp_id, emp_name, emp_level, mgr_id, sort) as
(
 select  
  a.emp_id, a.emp_name, 0, a.mgr_id,  
  a.emp_name
 from emp a
 where a.mgr_id is null

 union all

 select 
  b.emp_id, b.emp_name, emp_level + 1, b.mgr_id, 

  sort || ' : ' || coalesce( lpad(order_override::text, 10, '0'), b.emp_name )
 from emp b
 join org on org.emp_id = b.mgr_id
)
select 
 emp_id, repeat(' ', emp_level * 2) || emp_name as emp_name
from org
order by sort

输出:

 emp_id |            emp_name             
--------+---------------------------------
      1 | President
      2 |   Vice President
      3 |     CEO
      4 |     CTO
      5 |       Group Project Manager
      7 |         Project Manager 2
     14 |           Team Leader 2
     15 |             Software Engineer 3
     16 |             Software Engineer 4
     17 |           Test Lead 2
     19 |             Tester 4
     18 |             Tester 3
     20 |             Tester 5
      6 |         Project Manager 1
      8 |           Team Leader 1
      9 |             Software Engineer 1
     10 |             Software Engineer 2
     11 |           Test Lead 1
     12 |             Tester 1
     13 |             Tester 2
(20 rows)

项目经理 2 在项目经理 1 之前出现。测试人员 4 在测试人员 3 之前出现

如果存在 order_override(non-null)，该技术在于 b.name 的数字文本替换:

sort || ' : ' || coalesce( lpad(order_override::text, 10, '0'), b.emp_name )

以上代码是Postgres，要转换成Sql Server，去掉RECURSIVE，把REPEAT改成REPLICATE，|| 到 +。

等价于...

lpad(order_override::text, 10, '0')

...是:

RIGHT( REPLICATE('0',10) + CONVERT(VARCHAR, order_override), 10)