主页 / dba / 问题 / 47585
Accepted
Royi Namir
Asked: 2013-08-06 11:05:08 +0800 CST

CTE 查询没有打印出正确的结构？

  • 772

我有这张代表层次结构的表：

childID  parentID          NAME          siblingOrder
1          1               a               0
212        1               ab              1
112        1               ac              2
412        1               ad              3
-912       112             aca             0

结构是：

a
+----ab
+----ac
     +---aca
+----ad

（该siblingOrder列用于控制ab, ac,的顺序ad ）

我已经有了这个解决方案，它使用 siblingOrder：

;WITH CTE AS(
   SELECT childID, parentID, 0 AS depth, NAME , siblingOrder,
         CAST(RIGHT('00000' + CAST(siblingOrder AS VARCHAR(6)), 6)  AS VARCHAR(1024))  AS PATH
   FROM   @myTable
   WHERE   childID = parentID 
    UNION ALL
    SELECT  TBL.childID, TBL.parentID,
           CTE.depth + 1 , TBL.name ,TBL.siblingOrder,
           CAST(cte.Path + '.' + RIGHT('00000' + CAST(Tbl.siblingOrder AS VARCHAR(6)), 6)AS VARCHAR(1024) )
    FROM   @myTable AS TBL
            INNER JOIN CTE  ON  TBL.parentID = CTE.childID
    WHERE   TBL.childID<>TBL.parentID
)
SELECT path,depth, childID, parentID, siblingOrder, REPLICATE('----', depth) + name
FROM CTE
  ORDER BY PATH

所以order by PATH实际上做的工作：

在此处输入图像描述

问题：

问题是我必须（！）将值放入siblingOrder以使其工作！

否则，例如：

如果我把0所有siblingOrder这些都放在结果中：

在此处输入图像描述

（是的，现在按路径排序 - 不起作用......）

我需要它aca永远在ac

（我添加的唯一原因siblingOrder是订购兄弟姐妹！）而且我不想siblingOrder在不需要时强制添加

问题 ：

是否可以增强查询以便siblingOrder只影响兄弟姐妹？

我的意思是，如果我不关心兄弟姐妹的顺序（通过放置0），我仍然aca希望ac

sqlonline : 与 siblingOrder

Sqlonline - 没有 siblingOrder

sql-server cte

1 个回答

  1. Best Answer

    您需要在路径中包含parentID和。childID所以而不是

    RIGHT('00000' + CAST(Tbl.siblingOrder AS VARCHAR(6)), 6)

    你应该写

    RIGHT('0000000000' + CAST(Tbl.parentID AS VARCHAR(10)), 10) + RIGHT('00000' + CAST(Tbl.siblingOrder AS VARCHAR(6)), 6) + RIGHT('0000000000' + CAST(Tbl.childID AS VARCHAR(10)), 10)

    （那是在你的代码中的两个地方）

    通过这种方式，您可以在需要的地方提供 siblingOrder。如果存在，它将覆盖子订单。如果不存在，孩子将按其 childID 排序。

    没有 siblingOrder 的SQL Fiddle

    MS SQL Server 2008 架构设置：

    CREATE TABLE dbo.MyTable
    ([childID] int, [parentID] int, [NAME] varchar(3), [siblingOrder] int)
;

INSERT INTO dbo.MyTable
    ([childID], [parentID], [NAME], [siblingOrder])
VALUES
    (1, 1, 'a', 0),
    (212, 1, 'ab', 0),
    (112, 1, 'ac', 0),
    (412, 1, 'ad', 0),
    (-912, 112, 'aca', 0)
;

    查询 1：

    ;WITH CTE AS(
   SELECT childID, parentID, 0 AS depth, NAME , siblingOrder,
         CAST(RIGHT('0000000000' + CAST(parentID AS VARCHAR(10)), 10) + RIGHT('00000' + CAST(siblingOrder AS VARCHAR(6)), 6)+ RIGHT('0000000000' + CAST(childID AS VARCHAR(10)), 10)  AS VARCHAR(1024))  AS PATH
   FROM   dbo.MyTable 
   WHERE   childID = parentID 
    UNION ALL
    SELECT  TBL.childID, TBL.parentID,
           CTE.depth + 1 , TBL.name ,TBL.siblingOrder,
           CAST(cte.Path + '.' + RIGHT('0000000000' + CAST(Tbl.parentID AS VARCHAR(10)), 10) + RIGHT('00000' + CAST(Tbl.siblingOrder AS VARCHAR(6)), 6)+ RIGHT('0000000000' + CAST(Tbl.childID AS VARCHAR(10)), 10) AS VARCHAR(1024) )
    FROM   dbo.MyTable AS TBL
            INNER JOIN CTE  ON  TBL.parentID = CTE.childID
    WHERE   TBL.childID<>TBL.parentID
)
SELECT path,depth, childID, parentID, siblingOrder, REPLICATE('----', depth) + name
FROM CTE
  ORDER BY PATH

    结果：

    |                                                                             PATH | DEPTH | CHILDID | PARENTID | SIBLINGORDER |    COLUMN_5 |
----------------------------------------------------------------------------------------------------------------------------------------------
|                                                       00000000010000000000000001 |     0 |       1 |        1 |            0 |           a |
|                            00000000010000000000000001.00000000010000000000000112 |     1 |     112 |        1 |            0 |      ----ac |
| 00000000010000000000000001.00000000010000000000000112.0000000112000000000000-912 |     2 |    -912 |      112 |            0 | --------aca |
|                            00000000010000000000000001.00000000010000000000000212 |     1 |     212 |        1 |            0 |      ----ab |
|                            00000000010000000000000001.00000000010000000000000412 |     1 |     412 |        1 |            0 |      ----ad |

    SQL Fiddle与 siblingOrder

    MS SQL Server 2008 架构设置：

    CREATE TABLE dbo.MyTable
    ([childID] int, [parentID] int, [NAME] varchar(3), [siblingOrder] int)
;

INSERT INTO dbo.MyTable
    ([childID], [parentID], [NAME], [siblingOrder])
VALUES
    (1, 1, 'a', 0),
    (212, 1, 'ab', 1),
    (112, 1, 'ac', 3),
    (412, 1, 'ad', 2),
    (-912, 112, 'aca', 0)
;

    查询 1：

    ;WITH CTE AS(
   SELECT childID, parentID, 0 AS depth, NAME , siblingOrder,
         CAST(RIGHT('0000000000' + CAST(parentID AS VARCHAR(10)), 10) + RIGHT('00000' + CAST(siblingOrder AS VARCHAR(6)), 6)+ RIGHT('0000000000' + CAST(childID AS VARCHAR(10)), 10)  AS VARCHAR(1024))  AS PATH
   FROM   dbo.MyTable 
   WHERE   childID = parentID 
    UNION ALL
    SELECT  TBL.childID, TBL.parentID,
           CTE.depth + 1 , TBL.name ,TBL.siblingOrder,
           CAST(cte.Path + '.' + RIGHT('0000000000' + CAST(Tbl.parentID AS VARCHAR(10)), 10) + RIGHT('00000' + CAST(Tbl.siblingOrder AS VARCHAR(6)), 6)+ RIGHT('0000000000' + CAST(Tbl.childID AS VARCHAR(10)), 10) AS VARCHAR(1024) )
    FROM   dbo.MyTable AS TBL
            INNER JOIN CTE  ON  TBL.parentID = CTE.childID
    WHERE   TBL.childID<>TBL.parentID
)
SELECT path,depth, childID, parentID, siblingOrder, REPLICATE('----', depth) + name
FROM CTE
  ORDER BY PATH

    结果：

    |                                                                             PATH | DEPTH | CHILDID | PARENTID | SIBLINGORDER |    COLUMN_5 |
----------------------------------------------------------------------------------------------------------------------------------------------
|                                                       00000000010000000000000001 |     0 |       1 |        1 |            0 |           a |
|                            00000000010000000000000001.00000000010000010000000212 |     1 |     212 |        1 |            1 |      ----ab |
|                            00000000010000000000000001.00000000010000020000000412 |     1 |     412 |        1 |            2 |      ----ad |
|                            00000000010000000000000001.00000000010000030000000112 |     1 |     112 |        1 |            3 |      ----ac |
| 00000000010000000000000001.00000000010000030000000112.0000000112000000000000-912 |     2 |    -912 |      112 |            0 | --------aca |
    • 6

相关问题