MS SQL Server
  1. MS SQL Server
  3. Problème de logique Tricky – comment créer une solution basée sur un set pour cette requête?
Intereting Posts
Comment countr les occurrences d'une colonne calculée en SQL? requête SQL brute à laravel Pour split des données (text) dans une colonne particulière avec la condition requirejse dans MS SQL Raccourci SSMS pour naviguer entre les windows de requête ouvertes Le server Sql combine plusieurs sets de données sans dupliquer datatables Nom de colonne non valide lors de la création d'un triggersur à l'aide du server SQL Comment puis-je créer un MODÈLE DE REMPLACEMENT en SQL? Aujourd'hui contre le même jour les 3 dernières semaines SQL Server: récupération des loggings entre deux dates? Comment regrouper un champ date par semaine, où les semaines commencent le samedi? RANK, ROW_NUMBER sur T-SQL Que signifie @@ FETCH_STATUS <> – 1 ET @@ FETCH_STATUS <> – 2? Comment éviter la violation de la key unique par la mise à jour dans ASP MVC Quelles sont la key candidate, la key alternative, la key composite en SQL? appel à la fonction indéfinie mssql_connect () linux hosting

Problème de logique Tricky – comment créer une solution basée sur un set pour cette requête?

À l'aide de SQL Server 2008 et de toutes les fonctionnalités disponibles de TSQL, j'essaie de savoir comment procéder si une solution basée sur un set qui n'implique pas de table temporaire existe pour le problème suivant:

Étant donné un set de noeuds qui ont une relation parent-enfant, et un set de paires valeur-key qui s'appliquent à chacun, et étant donné que la valeur (pour une paire valeur-key donnée) à un niveau plus profond de la hiérarchie de noeud replacea une valeur avec la même key héritée d'un noeud ancêtre, select:

  1. l'set complet des paires key-valeur qui s'appliquent à un noeud donné
  2. l'set des valeurs héritées pour ce noeud

Le schéma est le suivant: 

create table Node ( ID bigint identity primary key, ParentID bigint null foreign key references Node(ID), Name nvarchar(100) ); create table KeyValuePair ( ID bigint identity primary key, KeyName nvarchar(100) not null, Value nvarchar(1000) not null, NodeID bigint not null foreign key references Node(ID), unique (KeyName, NodeID) );

Le jeu de résultats comprend essentiellement les colonnes KeyName , Value , InheritedValue .

J'ai essayé de le faire en utilisant une expression de table commune mais la logique est un peu compliquée.

Je configure les tables Node et KeyValuePair selon la question, et rempli avec quelques exemples de valeurs, de sorte que ma hiérarchie était la suivante:

Racine
| — A
| | — A1
| | — A2
|
| — B
| — B1
| — B2

J'ai assigné deux propriétés, nommées "Propriété 1" et "Propriété 2", chacune définie dans root avec les valeurs "Root Prop 1" et "Root Prop 2" respectivement. Dans A, je surclass "Propriété 1" avec la valeur "A Prop 1" et dans B, j'écarte "Propriété 2" avec la valeur "B Prop 2". 

 set identity_insert Node on insert into Node(ID,ParentID,Name) values (1,null,'Root'),(2,1,'A'),(3,1,'B'),(4,2,'A1'),(5,2,'A2'), (6,3,'B1'),(7,3,'B2') set identity_insert Node off insert into KeyValuePair(KeyName, [Value], NodeID) values ('Property 1','Root Prop 1',1), ('Property 2','Root Prop 2',1), ('Property 1','A Prop 1',2), ('Property 2','B Prop 2',3)

Appeler la solution de Nathan pour le noeud A1 ne donne aucune ligne!

La clause where dans la solution de Nathan doit être une condition de la jointure entre les keys et v, ce qui entraîne la procédure révisée ci-dessous (j'ai également renommé DataValue en KeyValuePair pour être cohérent avec la question d'origine): 

 create procedure dbo.ListDataValues @nodeid bigint as begin with nodes as ( select ID, ParentID, 0 as Level from Node n where ID=@nodeid union all select n.ID, n.ParentID, c.Level+1 as Level from Node n inner join nodes c on c.ParentID = n.ID ), keys as ( select distinct(KeyName) from KeyValuePair where NodeID in (select ID from nodes) ) select keys.KeyName, v.Value, i.Value as [InheritedValue], i.NodeID as [InheritedFromNodeID] from keys left join KeyValuePair v on v.KeyName = keys.KeyName and v.NodeID = @nodeid left join KeyValuePair i on i.KeyName = keys.KeyName and i.NodeID = (select top 1 NodeID from KeyValuePair d inner join nodes k on k.ID = d.NodeID where Level > 0 and d.KeyName = i.KeyName order by [Level]) end go

Cela a donné les résultats corrects comme prévu: 

 KeyName Value InheritedValue InheritedFromNodeID ------------ ------- ----------------- -------------------- Property 1 NULL A Prop 1 2 Property 2 NULL Root Prop 2 1

Vous devriez envisager d'utiliser le model de jeu nested pour stocker votre hiérarchie. Voici un lien qui le décrit: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

Il prend en charge une approche beaucoup plus conviviale SQL pour récupérer des informations communes sur les informations hiérarchiques.

Votre exigence ici pourrait alors être satisfaite avec une requête sur Node et une seule jointure à KeyValuePair.

Ok, je l'ai résolu moi-même. Il y a probablement d'autres façons de le faire, mais cela semble fonctionner assez bien: 

 create procedure dbo.ListDataValues @nodeid bigint as begin with nodes as ( select ID, ParentID, 0 as Level from Node n where ID=@nodeid union all select n.ID, n.ParentID, c.Level+1 as Level from Node n inner join nodes c on c.ParentID = n.ID ), keys as ( select distinct(KeyName) from DataValue where NodeID in (select ID from nodes) ) select v.KeyName, v.Value, i.Value as [InheritedValue], i.NodeID as [InheritedFromNodeID] from keys left join DataValue v on v.KeyName = keys.KeyName left join DataValue i on i.KeyName = keys.KeyName and i.NodeID = (select top 1 NodeID from DataValue d inner join nodes k on k.ID = d.NodeID where Level > 0 and d.KeyName = i.KeyName order by Level) where v.NodeID = @nodeid end

C'est l'un de ces cas où le path matérialisé brille. Voici comment j'ai résolu un problème similaire en l'utilisant:

Stockez vos parameters de configuration en tant que hiérarchie dans une database.