CTE : Afficher le dernier fils avec l'ID du top parent

**i.chafai** · 27/06/2018, 21h23

Bonjour,
c'est la première fois que je vais utiliser la récursivité et je me sens bloqué

J'ai une table des écritures comptables avec la structure suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
CREATE TABLE EcritureCompta
(cbMarq     int IDENTITY PRIMARY KEY , -- PK de la table
 JO_Num     varchar(7) , -- code journal comptable 
 EC_No      int , -- Code unique de l ecriture comptable
 EC_NoLink  int , -- Code de la nouvelle ecriture comptable genere dans le Report à nouveau
 JM_Date    datetime , -- Date de l'ecriture 
 EC_Montant numeric(24 , 6) , --Montant
 EC_Sens    smallint -- sens 0=Debit / 1=Credit
);

et petit jeu de données

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SET IDENTITY_INSERT [dbo].[EcritureCompta] ON 
GO
INSERT [dbo].[EcritureCompta] ([cbMarq], [JO_Num], [EC_No], [EC_NoLink], [JM_Date], [EC_Montant], [EC_Sens]) VALUES (1, N'CHQ', 15000, 25003, CAST(N'2014-05-01 00:00:00.000' AS DateTime), CAST(5000.000000 AS Numeric(24, 6)), 1)
INSERT [dbo].[EcritureCompta] ([cbMarq], [JO_Num], [EC_No], [EC_NoLink], [JM_Date], [EC_Montant], [EC_Sens]) VALUES (2, N'RAN', 25003, 36085, CAST(N'2015-01-01 00:00:00.000' AS DateTime), CAST(5000.000000 AS Numeric(24, 6)), 1)
INSERT [dbo].[EcritureCompta] ([cbMarq], [JO_Num], [EC_No], [EC_NoLink], [JM_Date], [EC_Montant], [EC_Sens]) VALUES (3, N'RAN', 36085, 895638, CAST(N'2016-01-01 00:00:00.000' AS DateTime), CAST(5000.000000 AS Numeric(24, 6)), 1)
INSERT [dbo].[EcritureCompta] ([cbMarq], [JO_Num], [EC_No], [EC_NoLink], [JM_Date], [EC_Montant], [EC_Sens]) VALUES (4, N'RAN', 895638, 3333, CAST(N'2017-01-01 00:00:00.000' AS DateTime), CAST(5000.000000 AS Numeric(24, 6)), 1)
INSERT [dbo].[EcritureCompta] ([cbMarq], [JO_Num], [EC_No], [EC_NoLink], [JM_Date], [EC_Montant], [EC_Sens]) VALUES (5, N'RAN', 6325, 0, CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(32000.000000 AS Numeric(24, 6)), 1)
INSERT [dbo].[EcritureCompta] ([cbMarq], [JO_Num], [EC_No], [EC_NoLink], [JM_Date], [EC_Montant], [EC_Sens]) VALUES (6, N'TRAIT', 5569, 6325, CAST(N'2018-05-01 00:00:00.000' AS DateTime), CAST(32000.000000 AS Numeric(24, 6)), NULL)
INSERT [dbo].[EcritureCompta] ([cbMarq], [JO_Num], [EC_No], [EC_NoLink], [JM_Date], [EC_Montant], [EC_Sens]) VALUES (7, N'RAN', 3333, 0, CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(5000.000000 AS Numeric(24, 6)), 1)
INSERT [dbo].[EcritureCompta] ([cbMarq], [JO_Num], [EC_No], [EC_NoLink], [JM_Date], [EC_Montant], [EC_Sens]) VALUES (8, N'RAN', 7777, 0, CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(960.000000 AS Numeric(24, 6)), 1)
SET IDENTITY_INSERT [dbo].[EcritureCompta] OFF
GO

j'ai créé une requête pour afficher l'arbre complète avec cette requete

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
WITH tree (Parent, Fils, Journal, Montant, Date, Level, Path) 
     AS (SELECT Ec_no, 
                Ec_nolink, 
                Jo_num, 
                Ec_montant, 
                Jm_date, 
                0, 
                Cast(Ec_no AS VARCHAR(max)) 
         FROM   Ecriturecompta 
         WHERE  Ec_nolink = 0 
         UNION ALL 
         SELECT Ec_no, 
                Ec_nolink, 
                Jo_num, 
                Ec_montant, 
                Jm_date, 
                t.Level + 1, 
                t.Path + '#' + Cast(Ec_no AS VARCHAR) 
         FROM   Ecriturecompta e 
                INNER JOIN tree t 
                        ON t.Parent = e.Ec_nolink) 
SELECT * 
FROM   tree 
ORDER  BY 7, 
          6

et voici le résultat :
Nom : Recuv.PNG
Affichages : 183
Taille : 10,7 Ko

Maintenant ce que je n'ai pas arriver de le faire est d'afficher chaque parent avec une nouvelle colonne contenant l'id du dernier fils.
par exemple :
- pour le Parent N° 3333 afficher une nouvelle colonne avec l'ID 15000 qui est son dernier fils.
- pour le parent N° 7777 afficher une nouvelle colonne avec l'ID 0 puisqu'il n'a pas de fils

Merci a vous

**i.chafai** · 28/06/2018, 00h00

Bonjour,
normalement j'ai trouvé la solution, je sais pas si c'est la solution optimale.
- j'ai ajouter la colonne topparent pour forcer l'affichage du top parent pour chaque ligne
- calculer le max niveau pour chaque top parent dans une nouvelle CTE
- faire une jointure entre les deux CTE pour afficher uniquement la dernière ligne

Merci de me corriger si vous trouvez si j'ai comité des erreurs

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
WITH tree( parent, fils, journal, montant, date, level, path, topparent) 
     AS (SELECT ec_no, 
                ec_nolink, 
                jo_num, 
                ec_montant, 
                jm_date, 
                0, 
                Cast(ec_no AS VARCHAR(max)) AS path, 
                ec_no 
         FROM   ecriturecompta 
         WHERE  ec_nolink = 0 
         UNION ALL 
         SELECT ec_no, 
                ec_nolink, 
                jo_num, 
                ec_montant, 
                jm_date, 
                t.level + 1, 
                t.path + '#' + Cast(ec_no AS VARCHAR), 
                t.topparent 
         FROM   ecriturecompta AS e 
                INNER JOIN tree AS t 
                        ON t.parent = e.ec_nolink), 
     lst (lst_lv, idparent) 
     AS (SELECT Max(level), 
                topparent 
         FROM   tree 
         GROUP  BY topparent) 
SELECT t.* 
FROM   tree t 
       INNER JOIN lst 
               ON t.topparent = lst.idparent 
                  AND t.level = lst.lst_lv

**SQLpro** · 28/06/2018, 20h06

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
WITH tree (Parent, TopNo, Step, Fils, Journal, Montant, Date, Level, Path) 
     AS (SELECT EC_No, EC_No, 1,
                EC_NoLink, 
                JO_Num, 
                EC_Montant, 
                JM_Date, 
                0, 
                Cast(EC_No AS VARCHAR(max)) 
         FROM   EcritureCompta 
         WHERE  EC_NoLink = 0 
         UNION ALL 
         SELECT EC_No, TopNo, Step + 1,
                EC_NoLink, 
                JO_Num, 
                EC_Montant, 
                JM_Date, 
                t.Level + 1, 
                t.Path + '#' + Cast(EC_No AS VARCHAR) 
         FROM   EcritureCompta e 
                INNER JOIN tree t 
                        ON t.Parent = e.EC_NoLink) 
SELECT * 
FROM   tree 
ORDER  BY TopNo, Step

A +

CTE : Afficher le dernier fils avec l'ID du top parent [2012]

Développement SQL Server

Discussions similaires

Partager

Partager