Início / dba / Perguntas / 47585
Accepted
Royi Namir
Asked: 2013-08-06 11:05:08 +0800 CST

A consulta CTE não imprime a estrutura correta?

  • 772

Eu tenho esta tabela que representa a hierarquia:

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 estrutura é:

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

(A siblingOrdercoluna é para controlar a ordem de ab, ac, ad )

Eu já tenho essa solução que usa o 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

Então order by PATH, na verdade, faça o trabalho:

insira a descrição da imagem aqui

O problema:

O problema é que devo (!) colocar valores no siblingOrderpara que funcione!

Caso contrário, por exemplo:

Se eu colocar 0tudo siblingOrderisso é o resultado:

insira a descrição da imagem aqui

(sim, agora classificando por caminho - não funciona...)

Eu preciso que acasempre estará sobac

(A única razão pela qual adicionei siblingOrderé para pedir irmãos!) e não quero forçar a adição siblingOrderquando não for necessário

Pergunta :

É possível aprimorar a consulta para que siblingOrderafete apenas os irmãos?

Quero dizer , se eu não me importo com a ordem dos irmãos ( colocando 0) , ainda espero acaque eles estejam sobac

Sqlonline: com siblingOrder

Sqlonline - sem pedido de irmão

sql-server cte

1 respostas

  1. Best Answer

    Você precisa incluir o parentIDe childIDno caminho. Então, em vez de

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

    você deveria escrever

    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)

    (Isso está em dois lugares no seu código)

    Usado dessa forma, você pode fornecer o siblingOrder onde desejar. Se presente, substituirá a ordem secundária. Se não estiverem presentes, as crianças serão ordenadas por seu childID.

    SQL Fiddle sem irmãoOrder

    Configuração do esquema do 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)
;

    Consulta 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

    Resultados :

    |                                                                             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 com irmãoOrder

    Configuração do esquema do 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)
;

    Consulta 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

    Resultados :

    |                                                                             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

relate perguntas