sql - 如何使用 t-sql 检查逻辑项组合？

Question

我必须检查 worker 是否具备所有必需的技能。这是通过将 worker 的技能集与所需的技能集进行比较来完成的。因此，为了更清楚地说明，这是我拥有的表的 DDL:

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

这是示例数据:

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')

这意味着有 3 组，每组定义了 3 种技能。

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

并且有具有以下技能的 worker :

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

现在，问题是在 Sql Server 2008 中编写一个函数，该函数接受 WorkerId 和 SetCombinator 参数，并返回一个值，指示 Worker 是否具备所有必需的技能。

示例输入 1:

WorkerId: 1
SetCombinator: OR

这意味着所有集合都应该有OR组合符，即:

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

结果应该是true，因为 worker 拥有与设置 #1 相对应的技能。

示例输入 2:

WorkerId: 4
SetCombinator: OR

结果应该是假。

示例输入 3:

WorkerId: 1
SetCombinator: AND

这意味着所有集合都应该有AND组合符，即:

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

结果应该是假，因为 worker 的技能仅对应于第 1 组，而不对应于第 2 组和第 3 组。

示例输入 4:

WorkerId: 5
SetCombinator: AND

结果应该是true，因为 worker 拥有与所有集合相对应的技能。

有什么想法这个函数应该是什么样子吗？

更新:我忘了提一下，RequiredSkills表中的技能不是常量，它们会经常被修改，而且这些技能的数量也是动态的。因此使用硬编码值的解决方案将不起作用。

Answer 1

这就是到目前为止我为您提供的信息。我希望您可以继续研究并进一步开发它来解决您的问题。我使用了按位逻辑 - 但我不是这方面的专家，这里可能还有其他人可以对此进行改进。

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