J'ai une table qui stocke les informations de l'entreprise et de leur société mère d'une manière hiérarchique régulière, avec companyid, parentid et nom.
Je viens d'apprendre une requête CTE dans Sql Server et écrire cette requête
WITH tableR (ParentCompanyID, CompanyID, Levels) AS ( -- Anchor member definition SELECT e.ParentCompanyID, e.CompanyID, 0 As Levels FROM tblCompany AS e WHERE ParentCompanyID in (9) UNION ALL -- Recursive member definition SELECT e.ParentCompanyID, e.CompanyID, Levels + 1 FROM tblCompany AS e INNER JOIN tableR AS d ON e.ParentCompanyID = d.CompanyID ) -- Statement that executes the CTE SELECT tabler.Levels, tableR.CompanyID, (left('--------------', (tabler.Levels* 2)) + c.CompanyName) as CName,c.ParentCompanyID FROM tableR Left join tblcompany c on tableR.CompanyId=c.CompanyID
Cela fonctionne bien, sauf qu'il list d'abord l'enfant de ID = 9, puis il list le premier niveau enfant et ensuite le niveau 2 .. et ainsi de suite, mais ce dont j'ai besoin est d'avoir datatables enfant juste sous leur parent,
L0 L1 L2 L1-1 L2-1 ....
Est-il possible de faire? Parce que sinon, je dois le faire récursivement dans le code C # que j'utilise.
J'essaie ça aussi
WITH tableR (ParentCompanyID, CompanyID, Levels, RowNumber) AS ( -- Anchor member definition SELECT e.ParentCompanyID, e.CompanyID, 1 As Levels, CAST((Row_Number() Over (Order by e.CompanyName) ) as Varchar(MAx)) as RowNumber FROM tblCompany AS e WHERE ParentCompanyID in (9) UNION ALL -- Recursive member definition SELECT e.ParentCompanyID, e.CompanyID, Levels + 1, CAST(Concat(d.RowNumber, CAST((Row_Number() Over (Order by e.CompanyName) ) as VARCHAR(MAX)) ) as VARCHAR(MAX)) as RowNumber FROM tblCompany AS e INNER JOIN tableR AS d ON e.ParentCompanyID = d.CompanyID ) -- Statement that executes the CTE SELECT tabler.Levels, RowNumber, tableR.CompanyID, (left('--------------', ((tabler.Levels - 1)* 2 )) + c.CompanyName) as CName,c.ParentCompanyID FROM tableR Left join tblcompany c on tableR.CompanyId=c.CompanyID order by RowNumber
Mais il échoue avec si un niveau a plus de 9 loggings.
Essayez cette solution:
DECLARE @Company TABLE ( CompanyID INT PRIMARY KEY, Name NVARCHAR(50) NOT NULL, ParentCompanyID INT NULL ); INSERT @Company (CompanyID,Name,ParentCompanyID) VALUES (8,N'Tomaten',NULL), (9,N'NON ĂNŞI chars',NULL), (10,N'Bananen',NULL), (11,N'Child #1',9), (12,N'Child #2',9), (13,N'Child #1.1',11), (14,N'Child #1.2',11); DECLARE @ParentCompanyID INT = 9; WITH RecComp AS ( SELECT crt.CompanyID, crt.Name, crt.ParentCompanyID, 1 AS Lvl, N'/' + CONVERT(NVARCHAR(4000),crt.CompanyID) + N'/' AS CompanyNode_AsChar FROM @Company crt WHERE crt.ParentCompanyID = @ParentCompanyID UNION ALL SELECT cld.CompanyID, cld.Name, cld.ParentCompanyID, prt.Lvl + 1, prt.CompanyNode_AsChar + CONVERT(NVARCHAR(4000), cld.CompanyID) + N'/' FROM RecComp prt -- parent INNER JOIN @Company cld ON prt.CompanyID = cld.ParentCompanyID ) SELECT *, CONVERT(HIERARCHYID, CompanyNode_AsChar) AS CompanyNode FROM RecComp ORDER BY CompanyNode;
Résultats:
CompanyID Name ParentCompanyID Lvl CompanyNode_AsChar CompanyNode --------- ---------- --------------- --- --------------------- ----------- 11 Child #1 9 1 /11/ 0xAE 13 Child #1.1 11 2 /11/13/ 0xAF6C 14 Child #1.2 11 2 /11/14/ 0xAF74 12 Child #2 9 1 /12/ 0xB2
Remarque: SQL Azure prend en charge le type de données Hierarchyid
Eh bien, la chose ios, vous n'avez pas de clause ORDER BY.
Pourquoi ne pas essayer au less
-- Statement that executes the CTE SELECT tabler.Levels, tableR.CompanyID, (left('--------------', (tabler.Levels* 2)) + c.CompanyName) as CName,c.ParentCompanyID FROM tableR Left join tblcompany c on tableR.CompanyId=c.CompanyID ORDER BY tableR.Levels
Cela dit, la section affichage / interface user devrait probablement être prise en charge par la sortie de l'interface user, et non par votre requête.
Cela semble presque comme si vous souhaitez le diffuser dans un TreeView plutôt.
Bon, finalement j'ai trouvé une solution à émettre. Afin d'get des critiques et si j'ai raison d'aider les autres ici, il est
WITH tableR (ParentCompanyID, CompanyID, Levels, RowNumber, RowNumber2) AS ( -- Anchor member definition SELECT e.ParentCompanyID, e.CompanyID, 1 As Levels, CAST((Row_Number() Over (Order by e.CompanyName) ) as Varchar(MAx)) as RowNumber, CAST( (Left('000', 3-Len(CAST((Row_Number() Over (Order by e.CompanyName) ) as Varchar(MAx)))) + CAST((Row_Number() Over (Order by e.CompanyName) ) as Varchar(MAx))) As VARCHAR(MAX) ) AS RowNumber2 FROM tblCompany AS e WHERE ParentCompanyID in (370) UNION ALL -- Recursive member definition SELECT e.ParentCompanyID, e.CompanyID, Levels + 1, CAST((Row_Number() Over (Order by e.CompanyName) ) as Varchar(MAx)) as RowNumber1, CAST( Concat(d.RowNumber2, Left('000', 3-Len(CAST((Row_Number() Over (Order by e.CompanyName) ) as Varchar(MAx)))), CAST((Row_Number() Over (Order by e.CompanyName) ) as Varchar(MAx)) ) as VARCHAR(MAX)) as RowNumber2 FROM tblCompany AS e INNER JOIN tableR AS d ON e.ParentCompanyID = d.CompanyID ) -- Statement that executes the CTE SELECT tabler.Levels, RowNumber, RowNumber2, tableR.CompanyID, (left('--------------', ((tabler.Levels - 1)* 2 )) + c.CompanyName) as CName,c.ParentCompanyID FROM tableR Left join tblcompany c on tableR.CompanyId=c.CompanyID order by RowNumber2, CName
Maintenant, voici l'explication:
Problème connu: il est renversé lorsque vous click Anchor query output> 10 rows, ou infact sur n'importe quelle ligne de plus de 10 rows, alors la requête anchor donne l'ID 11, et l'enfant 111 et confond la sortie.
Solution pour le problème ci-dessus: Je modifie ma requête pour utiliser GAUCHE et append 000, donc si je vois que je peux max de 100 enfants je mets 3 zéro si vous voyez 4 alors utilisez 0000 et changez 3 à 4 dans la requête.
MAIS: Je recommand fortement de répondre ci-dessus car c'est la façon de le faire.
Je voudrais partager ceci
Si vous voulez order des données … alphabétique et datatables enfant viennent juste sous leur parent .. créer un baseCTE, utiliser row_number au lieu de CompanyID, appelez la requête Anchor de la base CTE
BASE CTE
ROW_NUMBER() OVER ( PARTITION BY ParentCompanyID ORDER BY CompanyName) as [row_number]
Requête d'ancrage
N'/' + CONVERT(NVARCHAR(4000),[row_number]) + N'/' AS CompanyNode_AsChar
Requête récursive
prt.CompanyNode_AsChar + CONVERT(NVARCHAR(4000), [row_number]) + N'/'