sql-server - 带订单树的递归 SQL 查询

Question

我在将查询排序为树时遇到问题

WITH UtHierarchy
AS (
    SELECT etabid
        ,ut
        ,utlib
        ,parenteut
        ,0 AS LEVEL
        ,ut AS root
    FROM RUT
    WHERE etabid = 1
        AND parenteut IS NULL

    UNION ALL

    SELECT RUT.etabid
        ,RUT.ut
        ,RUT.utlib
        ,RUT.parenteut
        ,LEVEL + 1 AS LEVEL
        ,RUT.parenteut AS root
    FROM RUT
    INNER JOIN UtHierarchy uh ON uh.ut = rut.parenteut
    WHERE RUT.ETABID = 1
    )
SELECT *
FROM UtHierarchy
ORDER BY root

我需要有以下树:

UT RootUT Root-- UT level 1UT Root-- UT level 1-- -- UT level 2UT Root

This is working as intended for level 0 or 1, but for higher level it s broken. I try to select in root column the 'level 0' parent to order by root and ut, but after some time on this problem, I can't :(

How to resolve this ?

Thanks for your help.

EDIT : Thank you for editing with sql colors :)I've seen the solution for the topmost level but user has deleted his post.

WITH UtHierarchy 
AS (
  SELECT etabid
  ,ut
  ,utlib
  ,parenteut,
  0 as profondeur,
  ut as root
  FROM RUT 
  where etabid = 278
  and parenteut is null
  UNION  ALL
  SELECT RUT.etabid
  , RUT.ut
  , RUT.utlib
  , RUT.parenteut
  , profondeur + 1 as profondeur
  , root as root 
  FROM RUT 
  inner join UtHierarchy uh on uh.ut = rut.parenteut
  where RUT.ETABID = 278
)
select ut, parenteut, profondeur, root 
from UtHierarchy
order by root

但它也不起作用

这是一个包含真实数据的示例

ut  parenteutlevel  root10  1   1   111  1   1   112  1   1   113  1   1   114  1   1   1130 13  2   1131 13  2   1132 13  2   1133 13  2   1134 13  2   1135 13  2   1136 13  2   1120 12  2   1121 12  2   1122 12  2   1110 11  2   1111 11  2   1112 11  2   1113 11  2   1114 11  2   1115 11  2   1116 11  2   1101 10  2   1102 10  2   1103 10  2   1104 10  2   1105 10  2   1106 10  2   1107 10  2   11       0   1

正如你所看到的，这不是一个好的结构。我需要一棵这样的树:

ut  parenteutlevel  root1       0   110  1   1   1101 10  2   1102 10  2   1103 10  2   1104 10  2   1105 10  2   1106 10  2   1107 10  2   111  1   1   1110 11  2   1111 11  2   1112 11  2   1113 11  2   1114 11  2   1115 11  2   1116 11  2   112  1   1   1120 12  2   1121 12  2   1122 12  2   113  1   1   1130 13  2   1131 13  2   1132 13  2   1133 13  2   1134 13  2   1135 13  2   1136 13  2   114  1   1   1    

Answer 1

既然递归调用是正确的，你的问题就在于结果的排序

ORDER BY root

您可以尝试创建一个排序路径来帮助它们按正确的顺序排列:

WITH UtHierarchy
AS (
    SELECT etabid
        ,ut
        ,utlib
        ,parenteut
        ,0 AS LEVEL
        ,ut AS root
        ,RIGHT('000000' + CAST(ut AS varchar(MAX)), 6) AS sort
    FROM RUT
    WHERE etabid = 1
        AND parenteut IS NULL

    UNION ALL

    SELECT RUT.etabid
        ,RUT.ut
        ,RUT.utlib
        ,RUT.parenteut
        ,LEVEL + 1 AS LEVEL
        ,RUT.parenteut AS root
        ,uh.sort+'/'+RIGHT('000000' + CAST(RUT.ut AS varchar(20)), 6) AS sort
    FROM RUT
    INNER JOIN UtHierarchy uh ON uh.ut = rut.parenteut
    WHERE RUT.ETABID = 1
    )
SELECT *
FROM UtHierarchy
ORDER BY sort

编辑:

CASE => CAST(拼写错误)

编辑 2(从测试数据添加工作示例):

这里有一个复制并粘贴的测试代码。对我来说效果很好:

SELECT 10 AS ut, 1 AS parenteut
INTO #RUT
UNION ALL SELECT 11, 1
UNION ALL SELECT 12, 1
UNION ALL SELECT 13, 1
UNION ALL SELECT 14, 1
UNION ALL SELECT 130, 13
UNION ALL SELECT 131, 13
UNION ALL SELECT 132, 13
UNION ALL SELECT 133, 13
UNION ALL SELECT 134, 13
UNION ALL SELECT 135, 13
UNION ALL SELECT 136, 13
UNION ALL SELECT 120, 12
UNION ALL SELECT 121, 12
UNION ALL SELECT 122, 12
UNION ALL SELECT 110, 11
UNION ALL SELECT 111, 11
UNION ALL SELECT 112, 11
UNION ALL SELECT 113, 11
UNION ALL SELECT 114, 11
UNION ALL SELECT 115, 11
UNION ALL SELECT 116, 11
UNION ALL SELECT 101, 10
UNION ALL SELECT 102, 10
UNION ALL SELECT 103, 10
UNION ALL SELECT 104, 10
UNION ALL SELECT 105, 10
UNION ALL SELECT 106, 10
UNION ALL SELECT 107, 10
UNION ALL SELECT 1, 0;

WITH UtHierarchy
AS (
    SELECT
        ut
        ,parenteut
        ,0 AS LEVEL
        ,ut AS root
        ,RIGHT('000000' + CAST(ut AS varchar(MAX)), 6) AS sort
    FROM #RUT
    WHERE
        parenteut = 0

    UNION ALL

    SELECT 
        RUT.ut
        ,RUT.parenteut
        ,LEVEL + 1 AS LEVEL
        ,RUT.parenteut AS root
    ,uh.sort+'/'+RIGHT('000000' + CAST(RUT.ut AS varchar(20)), 6) AS sort
    FROM #RUT AS RUT
    INNER JOIN UtHierarchy uh ON uh.ut = rut.parenteut    
    )
SELECT *
FROM UtHierarchy
ORDER BY sort

DROP TABLE #RUT;