MS SQL Server
  1. MS SQL Server
  3. Ecriture d'une requête SQL récursive sur une table auto-référencée
Intereting Posts
Impossible de tronquer la table car elle est référencée par une contrainte FOREIGN KEY? EntityType n'a pas de key définie CodeIgniter: erreur de database s'est produite sur MSSQL Server 2008 R2 comment créer le proc stocké avec le paramètre dans sqlserver Suppression de duplicates dans une série chronologique Comment faire pour échapper les guillemets doubles dans la requête bcp sql dans le server SQL de la procédure stockée Microsoft.Data.Schema.ScriptDom avec VS2012 Ultimate Pourquoi le système stocké procs échoue-t-il avec le propriétaire inclus? Powershell de Job SQL Server Agent ne reconnaît pas ConvertTo-CSV Sélection de la colonne et max Roll up count dans la table hiérarchique (table auto-référentielle) Cette extension nécessite le pilote Microsoft ODBC 11 pour SQL Server pour communiquer avec SQL Server Après avoir changé la largeur de la colonne dans une table dans le server sql, ai-je besoin de recréer l'index? En sql divisez row1 par row, row2 par row3 …. .et stocker la troisième colonne de sortie Syntaxe de mise à jour SQL avec sous-requête

Ecriture d'une requête SQL récursive sur une table auto-référencée

J'ai une database avec une table appelée Items, qui contient ces colonnes:

  • ID – key primaire, uniqueidentifier
  • Nom – nvarchar (256)
  • ParentID – uniqueidentifier

Le champ name peut être utilisé pour créer un path vers l'élément, en parcourant chaque ParentId jusqu'à ce qu'il soit égal à '11111111-1111-1111-1111-111111111111', qui est un élément racine.

Donc, si vous aviez une table qui avait des lignes comme 

ID Name ParentID ------------------------------------------------------------------------------------- 11111111-1111-1111-1111-111111111112 grandparent 11111111-1111-1111-1111-111111111111 22222222-2222-2222-2222-222222222222 parent 11111111-1111-1111-1111-111111111112 33333333-3333-3333-3333-333333333333 widget 22222222-2222-2222-2222-222222222222

Donc, si je cherchais un object avec l'id '33333333-3333-3333-3333-333333333333' dans l'exemple ci-dessus, je voudrais que le path 

 /grandparent/parent/widget

revenu. J'ai essayé d'écrire un CTE, car il semblerait que vous fassiez normalement quelque chose comme ça – mais comme je ne fais pas beaucoup de SQL, je n'arrive pas à comprendre où je vais. J'ai regardé quelques exemples, et ceci est aussi proche que je peux l'être – ce qui ne fait que ramener la rangée des enfants. 

 declare @id uniqueidentifier set @id = '10071886-A354-4BE6-B55C-E5DBCF633FE6' ;with ItemPath as ( select a.[Id], a.[Name], a.ParentID from Items a where Id = @id union all select parent.[Id], parent.[Name], parent.ParentID from Items parent inner join ItemPath as a on a.Id = parent.id where parent.ParentId = a.[Id] ) select * from ItemPath

Je n'ai aucune idée de la façon dont je déclarerais une variable locale pour le path et continuer à l'append dans la requête récursive. J'allais essayer d'get au less toutes les lignes au parent avant d'aller après cela. Si quelqu'un pouvait aider avec ça aussi – je l'apprécierais.

Eh bien, voici la solution de travail

Exemple SQL FIDDLE 

 declare @id uniqueidentifier set @id = '33333333-3333-3333-3333-333333333333' ;with ItemPath as ( select a.[Id], a.[Name], a.ParentID from Items a where Id = @id union all select parent.[Id], parent.[Name] + '/' + a.[Name], parent.ParentID from ItemPath as a inner join Items as parent on parent.id = a.parentID ) select * from ItemPath where ID = '11111111-1111-1111-1111-111111111112'

Je ne l'aime pas beaucoup, je pense que la meilleure solution sera de le faire autrement. Attendez une minute et j'essaye d'écrire une autre requête 🙂

UPDATE ici c'est

Exemple SQL FIDDLE 

 create view vw_Names as with ItemPath as ( select a.[Id], cast(a.[Name] as nvarchar(max)) as Name, a.ParentID from Items a where Id = '11111111-1111-1111-1111-111111111112' union all select a.[Id], parent.[Name] + '/' + a.[Name], a.ParentID from Items as a inner join ItemPath as parent on parent.id = a.parentID ) select * from ItemPath

et maintenant vous pouvez utiliser cette vue 

 declare @id uniqueidentifier set @id = '33333333-3333-3333-3333-333333333333' select * from vw_Names where Id = @id

J'avais besoin d'une version légèrement différente de cette réponse car je voulais produire une list de toutes les lignées dans l'tree. Je voulais aussi connaître la profondeur de chaque nœud. J'ai ajouté une table temporaire de parents de niveau supérieur que je pourrais boucler et une table temporaire pour build le jeu de résultats. 

  use Items Select * Into #Temp From Items where ParentID=0 Declare @Id int create table #Results ( Id int, Name nvarchar(max), ParentId int, Depth int ) While (Select Count(*) From #Temp) > 0 Begin Select Top 1 @Id = Id From #Temp begin with ItemPath as ( select a.[Id], cast(a.[Name] as nvarchar(max))as Name, a.ParentID ,1 as Depth from Items a where a.ID = @id union all select a.[Id], parent.[Name] + '/' + a.[Name], a.ParentID, 1 + Depth from Items as a inner join ItemPath as parent on parent.id = a.parentID ) insert into #Results select * from ItemPath end Delete #Temp Where Id = @Id End drop table #Temp select * from #Results drop table #Results

Si nous commençons à partir du tableau suivant … 

 Id Name ParentID 1 Fred 0 2 Mary 0 3 Baker 1 4 Candle 2 5 Stick 4 6 Maker 5

Nous obtiendrions cette table de résultat. 

 Id Name ParentID Depth 1 Fred 0 1 2 Mary 0 1 3 Fred/Baker 1 2 4 Mary/Candle 2 2 5 Mary/Candle/Stick 4 3 6 Mary/Candle/Stick/Maker 5 4