MS SQL Server
  1. MS SQL Server
  3. Comment vérifier les combinaisons d'éléments logiques en utilisant t-sql?
Intereting Posts
Qu'est-ce que TDS Protocol Version 8.0 et pourquoi devrais-je l'utiliser? Créer un index sur la vue SQL avec les opérateurs UNION? Cela va-t-il vraiment améliorer les performances? Interrogation d'une table dans SQL Server en fonction de la permutation des colonnes 2 et 3 Mauvais plan pour la condition CONTAINS dans le MS SQL Comment puis-je normaliser une table en extrayant du text séparé par des virgules d'une colonne dans une table enfant? Recherche en text intégral SQL Server pour le contenu Latex Chargement de plusieurs grands DataTables / DataReaders ADO.NET – Amélioration des performances Comment transformer la list de hierarchyid en un tree binary Le regroupement de connections fonctionne-t-il correctement dans Subsonic? Accès à SQLServer avec Node.js sous Linux Comment utiliser SQL Server 2012 comme database dans codeigniter Db Connection simple file, je ne me souviens pas de l'extension Déclencheurs dans SQL Server Fusionner datatables Defaut et datatables ajustées par l'user T-SQL: paramètre sp_MSdependencies Flags

Comment vérifier les combinaisons d'éléments logiques en utilisant t-sql?

Je dois vérifier si un travailleur possède toutes les compétences requirejses. Ceci est fait en comparant l'set des compétences du travailleur avec un set de compétences requirejses. Donc, pour le rendre plus clair, voici le DDL des tables que j'ai: 

CREATE TABLE [WorkerSkills]( [WorkerId] [bigint] NOT NULL, [SkillName] [varchar](100) NOT NULL ) GO CREATE TABLE [SkillCombinator]( [SetId] [int] NOT NULL, [SkillCombinator] [varchar](5) NOT NULL ) GO CREATE TABLE [RequiredSkills]( [SetId] [int] NOT NULL, [SkillName] [varchar](100) NOT NULL ) GO

et voici les exemples de données: 

 INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (1, 'A') INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (1, 'B') INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (1, 'C') INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (2, 'D') INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (2, 'X') INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (3, 'E') INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (4, 'A') INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (4, 'B') INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (4, 'H') INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (4, 'I') INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (5, 'A') INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (5, 'B') INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (5, 'C') INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (5, 'E') INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (5, 'G') INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (5, 'H') INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (5, 'I') INSERT [SkillCombinator] ([SetId], [SkillCombinator]) VALUES (1, 'AND') INSERT [SkillCombinator] ([SetId], [SkillCombinator]) VALUES (2, 'OR') INSERT [SkillCombinator] ([SetId], [SkillCombinator]) VALUES (3, 'AND') INSERT [RequiredSkills] ([SetId], [SkillName]) VALUES (1, 'A') INSERT [RequiredSkills] ([SetId], [SkillName]) VALUES (1, 'B') INSERT [RequiredSkills] ([SetId], [SkillName]) VALUES (1, 'C') INSERT [RequiredSkills] ([SetId], [SkillName]) VALUES (2, 'D') INSERT [RequiredSkills] ([SetId], [SkillName]) VALUES (2, 'E') INSERT [RequiredSkills] ([SetId], [SkillName]) VALUES (2, 'F') INSERT [RequiredSkills] ([SetId], [SkillName]) VALUES (3, 'G') INSERT [RequiredSkills] ([SetId], [SkillName]) VALUES (3, 'H') INSERT [RequiredSkills] ([SetId], [SkillName]) VALUES (3, 'I')

Cela signifie qu'il y a 3 sets avec chacun ayant 3 compétences définies. 

 set 1: A and B and C set 2: D or E or F set 3: G and H and I

Et il y a des travailleurs avec les compétences suivantes: 

 worker 1: A, B, C worker 2: D, X worker 3: E worker 4: A, B, H, I worker 5: A, B, C, E, G, H, I

Maintenant, le problème est d'écrire une fonction dans Sql Server 2008 qui accepte les parameters WorkerId et SetCombinator et renvoie une valeur indiquant si Worker a toutes les compétences requirejses.

Exemple d'input 1: 

 WorkerId: 1 SetCombinator: OR

Cela signifie que tous les sets devraient avoir un combinateur OR , c'est-à-dire: 

 set 1: A and B and C OR set 2: D or E or F OR set 3: G and H and I

Le résultat devrait être vrai puisque Worker a des compétences qui correspondent à l'set # 1.

Exemple d'input 2: 

 WorkerId: 4 SetCombinator: OR

Le résultat devrait être faux .

Exemple d'input 3: 

 WorkerId: 1 SetCombinator: AND

Cela signifie que tous les sets doivent avoir un combinateur ET , c'est-à-dire: 

 set 1: A and B and C AND set 2: D or E or F AND set 3: G and H and I

Le résultat devrait être faux puisque Worker a des compétences qui correspondent uniquement à l'set n ° 1, mais pas aux deuxième et troisième sets.

Exemple d'input 4: 

 WorkerId: 5 SetCombinator: AND

Le résultat devrait être vrai puisque le travailleur a des compétences qui correspondent à tous les sets.

Des idées à quoi cette fonction devrait ressembler?

MISE À JOUR: J'ai oublié de mentionner que les compétences dans la table RequiredSkills ne sont pas constantes, elles seront souvent modifiées, et le nombre de ces compétences est également dynamic. Donc, la solution avec des valeurs codées en dur ne fonctionnera pas.

C'est ce que j'ai pour vous jusqu'à présent. J'espère que c'est quelque chose que vous pouvez travailler et développer plus loin pour correspondre à votre problème. J'ai utilisé la logique des bits – mais je ne suis pas expert en la matière, et il y en a peut-être d'autres ici qui pourraient améliorer cela. 

 DECLARE @WorkerID BIGINT = 1, @LogicalCase BIT = 1, -- [0 = OR], [1 = AND] @SkillScore INT = 0, @isTrue BIT = 0 SELECT @SkillScore = SUM ( CASE WHEN SkillName = 'A' THEN 1 WHEN SkillName = 'B' THEN 2 WHEN SkillName = 'C' THEN 4 WHEN SkillName = 'D' THEN 8 WHEN SkillName = 'E' THEN 16 WHEN SkillName = 'F' THEN 32 WHEN SkillName = 'G' THEN 64 WHEN SkillName = 'H' THEN 128 WHEN SkillName = 'I' THEN 256 END ) FROM WorkerSkills WHERE WorkerID = @WorkerID IF @LogicalCase = 0 BEGIN IF (@SkillScore & 7 = 7) -- set 1: A and B and C OR ( (@SkillScore & 8 = 8) OR (@SkillScore & 16 = 16) OR (@SkillScore & 32 = 32) ) -- OR set 2: D or E or F OR (@SkillScore & 448 = 448) -- OR set 3: G and H and I BEGIN SET @isTrue = 1 END END IF @LogicalCase = 1 BEGIN IF (@SkillScore & 7 = 7) -- set 1: A and B and C AND ( (@SkillScore & 8 = 8) OR (@SkillScore & 16 = 16) OR (@SkillScore & 32 = 32) ) -- AND set 2: D or E or F AND (@SkillScore & 448 = 448) -- AND set 3: G and H and I BEGIN SET @isTrue = 1 END END SELECT @isTrue

peut-être pas le plus efficace mais cela fonctionne pour n'importe quel nombre de compétences et de combinaisons: 

 declare @workerid int =5 -- input param declare @setcombinator varchar(3) ='AND' -- input param declare @skillsleft varchar(max) declare @result varchar(10) declare @getsets cursor declare @set int set @getsets = CURSOR FOR SELECT distinct SetId FROM SkillCombinator OPEN @getsets FETCH NEXT FROM @getsets INTO @set -- set result by default IF(@setcombinator='OR') set @result='FALSE' else set @result='TRUE' WHILE @@FETCH_STATUS = 0 BEGIN -- compares each skillset against worker's and determine match or mismatch if (select skillcombinator from skillcombinator where setid=@set) = 'AND' BEGIN --needs all the skills in the set SET @skillsleft= (select count(*) from (select skillname from RequiredSkills a join SkillCombinator b on a.setid=b.setid where a.setid=@set except select skillname from workerskills where workerid=@workerid) t) -- override default depending on the logical combinations if (@skillsleft = 0 and @setcombinator='OR') set @result='TRUE' if (@skillsleft > 0 and @setcombinator='AND')set @result='FALSE' END if (select skillcombinator from skillcombinator where setid=@set) = 'OR' BEGIN --needs at least one of the skills in the set SET @skillsleft= (select count(*) from (select skillname from RequiredSkills a join SkillCombinator b on a.setid=b.setid where a.setid=@set intersect select skillname from workerskills where workerid=@workerid) t) -- override default depending on the logical combinations if (@skillsleft > 0 and @setcombinator='OR') set @result='TRUE' if (@skillsleft = 0 and @setcombinator='AND') set @result='FALSE' END FETCH NEXT FROM @getsets INTO @set END select @result CLOSE @getsets DEALLOCATE @getsets

Ok, alors voici la meilleure solution que j'ai trouvée jusqu'ici. 

 create function fnMatchedToSkillsSet ( @WorkerId int, @Condition varchar(3) ) returns table as return ( with x as ( select sc.SetId, nullif(case when sc.SkillCombinator = 'AND' and count(distinct ws.SkillName) = count(*) then count(distinct ws.SkillName) when sc.SkillCombinator = 'OR' then count(distinct ws.SkillName) end, 0) as SkillsCount from dbo.SkillCombinator sc join dbo.RequiredSkills rs on rs.SetId = sc.SetId left join dbo.WorkerSkills ws on ws.WorkerId = @WorkerId and ws.SkillName = rs.SkillName group by sc.SetId, sc.SkillCombinator ) select case when @Condition = 'AND' and count(SkillsCount) = (select count(*) from dbo.SkillCombinator) then 1 when @Condition = 'OR' and count(SkillsCount) > 0 then 1 else 0 end as Result from x );