MS SQL Server
  1. MS SQL Server
  3. Expressions de table communes pour récupérer le path
Intereting Posts
Tilde (~) Fichier délimité lu dans SSIS SqlDataAdapter ferme-t-il la fonction SqlConnection après Fill ()? Calcul du nombre d'loggings après avoir effectué un GROUP BY dans SQL Comment accélérer les instructions select dans SQL Server Où puis-je get une list de codes postaux américains avec des géocodes de latitude et de longitude? Utilisation de IF – ELSE dans SQL Server Insert Query Obtenir un appel à un travail et le résultat d'une sélection complexe dans une procédure unique dans Microsoft SQL Combiner SQL avec XQuery et Linq To SQL Utilisation de NOLOCK sur une table qui est jointe à elle-même sql select un problème de requête Modifier une colonne: null à non null Implémenter pour une requête server SQL Mises à jour en time réel entre l'application de bureau C #, l'application mobile et le server SQL Création d'une vue temporaire à partir d'une table temporaire dans SQL Server Comment find la couleur la plus proche dans SQL?

Expressions de table communes pour récupérer le path

J'essaie d'utiliser une requête récursive pour find un path à travers une table structurée comme ceci:

RelatedEntities 

FromKey TINYINT ToKey TINYINT ...more....

Je pensais pouvoir faire quelque chose comme ça: 

 DECLARE @startKey UNIQUEIDENTIFIER, @endKey UNIQUEIDENTIFIER; SET @startKey = 0; SET @endKey = 3; ;With findPath AS ( SELECT FromKey, ToKey FROM RelatedEntities WHERE FromKey = @startKey UNION ALL SELECT FromKey, ToKey FROM RelatedEntities r JOIN findPath b ON r.FromKey = b.ToKey AND r.FromKey NOT IN (SELECT FromKey FROM b) ) SELECT * FROM findPath;

Ce code échoue car je ne peux pas utiliser une sous-requête dans un CTE. Il semble également que la requête récursive ne peut contenir qu'une seule reference au CTE. (vrai?) Peut-être que c'est un travail pour un slider, ou un code de procédure, mais je pensais que je le mettrais ici au cas où je manque un moyen de find un path à travers une table avec un CTE?

Les parameters sont:

  1. Commencez avec une key de début et de fin
  2. La requête de base utilise la key de début
  3. La requête récursive devrait s'arrêter quand elle contient la key de fin, (n'a pas été capable de la comprendre) et ne devrait pas répéter les keys de démarrage.
  4. Une option MAXRECURSION peut être utilisée pour s'arrêter après un certain nombre d'itérations.

Merci à tous les gourous du CTE. Réglez-moi directement.

Changer cela de UNIQUEIDENTIFIERS à TINYINT pour la lisibilité. Les constructions SQL sont les mêmes. Voici du code pour le tester. 

 CREATE TABLE RelatedEntities(FromKey TINYINT, ToKey TINYINT); INSERT RelatedEntities(FromKey, ToKey) VALUES (1, 0), (0, 1), (1, 7), (7, 1), (3, 4), (4, 3) ;With FindPath AS ( SELECT FromKey, ToKey, 0 AS recursionLevel FROM RelatedEntities WHERE FromKey = 1 UNION ALL SELECT r.FromKey, r.ToKey, recursionLevel = recursionLevel + 1 FROM RelatedEntities r INNER JOIN FindPath b ON r.FromKey = b.ToKey WHERE b.ToKey <> 3 AND RecursionLevel < 10 ) SELECT * FROM FindPath ORDER BY recursionLevel

Notez que cela revient de 1, à 0, puis de 0 à 1, et se répète jusqu'à ce que je n'ai plus de niveaux de récursivité.

Vous devez modifier votre requête comme ceci: 

 DECLARE @startKey UNIQUEIDENTIFIER, @endKey UNIQUEIDENTIFIER; DECLARE @maxRecursion INT = 100 SET @startKey = '00000000-0000-0000-0000-000000000000'; SET @endKey = 'F7801327-C037-AA93-67D1-B7892F6093A7'; ;With FindPath AS ( SELECT FromKey, ToKey, 0 AS recursionLevel FROM RelatedEntities WHERE FromKey = @startKey UNION ALL SELECT r.FromKey, r.ToKey, recursionLevel = recursionLevel +1 FROM RelatedEntities r INNER JOIN FindPath b ON r.FromKey = b.ToKey WHERE b.ToKey <> @endKey AND recursionLevel < @maxRecursion ) SELECT * FROM FindPath;

Le membre d'ancrage du CTE ci-dessus: 

 SELECT FromKey, ToKey, 0 AS recursionLevel FROM RelatedEntities WHERE FromKey = @startKey

sélectionnera l' logging de départ , T0 , de la string d'loggings (De, À).

Le membre récursif du CTE : 

 SELECT r.FromKey, r.ToKey, recursionLevel = recursionLevel +1 FROM RelatedEntities r INNER JOIN FindPath b ON r.FromKey = b.ToKey WHERE b.ToKey <> @endKey AND recursionLevel < @maxRecursion

sera exécuté avec T0 , T1 , … comme input et T1 , T2 , … respectivement comme sortie.

Ce process continuera d'append des loggings à l'set de résultats final jusqu'à ce qu'un set vide soit renvoyé par le membre récursif, c'est-à-dire jusqu'à ce qu'un logging avec ToKey=@endKey ait été ajouté au jeu de résultats ou @maxRecursion niveau @maxRecursion ait été atteint.

MODIFIER:

Vous pouvez utiliser la requête suivante pour gérer efficacement les paths circulaires: 

 ;With FindPath AS ( SELECT FromKey, ToKey, 0 AS recursionLevel, CAST(FromKey AS VARCHAR(MAX)) AS FromKeys FROM RelatedEntities WHERE FromKey = 1 UNION ALL SELECT r.FromKey, r.ToKey, recursionLevel = recursionLevel + 1, FromKeys = FromKeys + ',' + CAST(r.FromKey AS VARCHAR(MAX)) FROM RelatedEntities r INNER JOIN FindPath b ON r.FromKey = b.ToKey WHERE (b.ToKey <> 3) AND (RecursionLevel < 10) AND PATINDEX('%,' + CAST(r.ToKey AS VARCHAR(MAX)) + ',%', ',' + FromKeys + ',') = 0 ) SELECT * FROM FindPath ORDER BY recursionLevel

Champ FromKeys est utilisé pour poursuivre FromKey sur le prochain niveau de récursivité. De cette façon, toutes les keys des niveaux de récursivité précédents sont accumulées de niveau en niveau en utilisant la concaténation de string . PATINDEX est ensuite utilisé pour vérifier si un path circulaire a été rencontré.

SQL Fiddle Demo ici