Faire une requête comme une arborescence

Question

Frères, pouvez-vous m'aider? Merci

                 Table A
       Id Name                 IdParent
       1   Operation            Null
       2   Developer             1
       3    Android              2
       4    IOS                  2

Résultat attendu:

       ID          Name
        1           +Operation
        2           +------ Developer
        3           +------------Android
        4           +------------ IOS

sql-server

2

Hart Hartono 26 avril 2017 à 18:17

Answer 1

Voici une autre version:

WITH RawData AS (
    SELECT 1 AS Id, 'Operation' AS Name, CONVERT(INT, NULL) AS IdParent
    UNION ALL
    SELECT 2 AS Id, 'Developer' AS Name, 1 AS IdParent
    UNION ALL
    SELECT 3 AS Id, 'Android' AS Name, 2 AS IdParent
    UNION ALL
    SELECT 4 AS Id, 'IOS' AS Name, 2 AS IdParent),
Depth AS (
    SELECT
        Id,
        1 AS depth,
        IdParent
    FROM
        RawData
    UNION ALL
    SELECT
        d.Id,
        d.depth + 1,
        r.IdParent
    FROM
        Depth d
        INNER JOIN RawData r ON r.Id = d.IdParent),
MaxDepth AS (
    SELECT
        Id,
        MAX(depth) AS depth
    FROM
        Depth
    GROUP BY
        Id)
SELECT
    r.Id,
    '+' + REPLICATE('----', m.depth - 1) + r.Name AS Name
FROM 
    RawData r
    INNER JOIN MaxDepth m ON m.Id = r.Id;

Résultats:

Id  Name
1   +Operation
2   +----Developer
3   +--------Android
4   +--------IOS

2

Richard Hansell 26 avril 2017 à 15:29

Answer 2

DECLARE @mockup TABLE(Id INT, Name VARCHAR(100), IdParent INT);
INSERT INTO @mockup VALUES
 (1,'Operation',Null)
,(2,'Developer',1)
,(3,'Android',2)
,(4,'IOS',2);

--La requête utilise un CTE récursif et enfin REPLICATE avec le niveau récursif pour ajouter le nombre de tirets ...

WITH recCTE AS
(
    SELECT Id, Name, 1 AS Lvl, CAST(REPLACE(STR(ROW_NUMBER() OVER (ORDER BY Id),5),' ','0') AS VARCHAR(MAX)) AS Seq
    FROM @mockup 
    WHERE IdParent IS NULL

    UNION ALL

    SELECT m.Id,m.Name,r.Lvl +1,r.Seq + '.' + REPLACE(STR(ROW_NUMBER() OVER (ORDER BY m.Id),5),' ','0')
    FROM @mockup AS m
    INNER JOIN recCTE AS r ON m.IdParent=r.Id
)
SELECT *
      ,'+' + REPLICATE('-',Lvl*4) + Name 
FROM recCTE 
ORDER BY Seq

Le résultat

+----+-----------+-----+----------------------+
| Id | Name      | Lvl | (Kein Spaltenname)   |
+----+-----------+-----+----------------------+
| 1  | Operation | 1   | +----Operation       |
+----+-----------+-----+----------------------+
| 2  | Developer | 2   | +--------Developer   |
+----+-----------+-----+----------------------+
| 3  | Android   | 3   | +------------Android |
+----+-----------+-----+----------------------+
| 4  | IOS       | 3   | +------------IOS     |
+----+-----------+-----+----------------------+

2

Shnugo 26 avril 2017 à 16:04

Answer 3

En ajoutant une séquence lors de la construction récursive, vous pouvez facilement créer la séquence de présentation et l'imbrication appropriées

Declare @YourTable table (id int,IdParent  int,Name varchar(50))
Insert into @YourTable values 
 ( 1, NULL,'Operation')
,( 2, 1   ,'Developer')
,( 3, 2   ,'Android')
,( 4, 2   ,'IOS')
,( 5, 1   ,'Poet')
,( 6, 5   ,'Limerick')
,( 7, 5   ,'Haiku')

Declare @Top    int         = null      --<<  Sets top of Hier Try 2
Declare @Nest   varchar(25) = '|-----'  --<<  Optional: Added for readability

;with cteP as (
      Select Seq  = cast(10000+Row_Number() over (Order by Name) as varchar(500))
            ,ID
            ,IdParent 
            ,Lvl=1
            ,Name 
      From   @YourTable 
      Where  IsNull(@Top,-1) = case when @Top is null then isnull(IdParent ,-1) else ID end
      Union  All
      Select Seq  = cast(concat(p.Seq,'.',10000+Row_Number() over (Order by r.Name)) as varchar(500))
            ,r.ID
            ,r.IdParent 
            ,p.Lvl+1
            ,r.Name 
      From   @YourTable r
      Join   cteP p on r.IdParent  = p.ID)
Select A.ID
      ,A.IdParent 
      ,A.Lvl
      ,Name = Replicate(@Nest,A.Lvl-1) + A.Name
 From ctep A
 Order By A.Seq

Retour

ID  IdParent    Lvl Name
1   NULL        1   Operation
2   1           2   |-----Developer
3   2           3   |-----|-----Android
4   2           3   |-----|-----IOS
5   1           2   |-----Poet
7   5           3   |-----|-----Haiku
6   5           3   |-----|-----Limerick

3

John Cappelletti 26 avril 2017 à 15:49

Faire une requête comme une arborescence

3 réponses

De nouvelles questions

sql-server