TSQL-查找与AND / OR过滤器匹配的行

debugcn 发表于 Dev

普拉迪普·巴拉克里希南（Pradeep Balakrishnan）

我正在尝试找到在SQL中执行此操作的最佳方法（或者是否建议将SQL用作所有方法）

我有几个表，其结构如下所述（为了便于说明而进行了归一化处理）。一个是带有“标签”定义的员工表。第二个是员工组表，以及该组的条件。

员工表

    EmployeeId      |   Tags
    ----------------------------- 
    1               |   Country|US
    1               |   EmployeeType|FullTime 
    2               |   Country|CA
    2               |   EmployeeType|FullTime
    3               |   Country|BR
    3               |   EmployeeType|FullTime
    4               |   Country|US
    4               |   EmployeeType|FullTime
    4               |   Language|Spanish

组表

    GroupId         |   Tags                           |    OperatorGroup
    --------------------------------------------------------------------------------------------- 
    1               |   Country|US                     |    D68BF531-FD9B-4A0E-886E-7420BDFFE380
    1               |   Country|CA                     |    D68BF531-FD9B-4A0E-886E-7420BDFFE380
    1               |   EmployeeType|FullTime          |    0B97D792-7659-42EF-8036-D19B1A0D5197
    2               |   Country|BR                     |    CE61BC17-2567-4B02-AE5E-F5EA40265EE7
    2               |   EmployeeType|FullTime          |    6A3418F6-C4FD-40E2-97B3-0F9E87759939
    3               |   Country|US                     |    BF3DAC05-8131-463F-976E-28099628842D
    3               |   EmployeeType|FullTime          |    D63CEDC2-D15F-4E05-B3B9-0109121B3C75
    3               |   Language|Spanish               |    3AA247D1-3779-48BD-8A2C-B9C8A084E32D

在上表“组”中，OperatorGroup确定该组的条件/运算符。在同一GroupId中，我在同一OperatorGroup中使用OR运算符，否则在AND运算符中使用。对于GroupId == 1，Country | US和Country | CA都具有相同的OperatorGroup，因此将通过OR子句连接，换句话说，“如果您所在的国家（美国或CA）并且您是全职员工”

我正在寻找的结果是该员工所属的组。根据以上数据，我希望以下内容

员工组结果

    EmployeeId         |   GroupId
    ------------------------------
    1                  |   1
    2                  |   1
    3                  |   2
    4                  |   1
    4                  |   3

仅SQL解决方案（TSQL-SQL Server 2012）是否过于雄心勃勃？无论哪种方式的任何指针，将不胜感激。

我应该补充一点，标记名称（国家，雇员类型和语言）仅是示例，我可以在运行时添加标记名称和值（美国，加拿大等）。

普拉迪普·巴拉克里希南（Pradeep Balakrishnan）

我解决了我不得不稍微更改组表的架构和规则

组表

GroupId         |   Tags                           |    OperatorGroup
--------------------------------------------------------------------------------------------- 
1               |   Country|US                     |    D68BF531-FD9B-4A0E-886E-7420BDFFE380
1               |   EmployeeType|FullTime          |    D68BF531-FD9B-4A0E-886E-7420BDFFE380
1               |   Country|CA                     |    0B97D792-7659-42EF-8036-D19B1A0D5197
1               |   EmployeeType|FullTime          |    0B97D792-7659-42EF-8036-D19B1A0D5197
2               |   Country|BR                     |    CE61BC17-2567-4B02-AE5E-F5EA40265EE7
2               |   EmployeeType|FullTime          |    CE61BC17-2567-4B02-AE5E-F5EA40265EE7
3               |   Country|US                     |    BF3DAC05-8131-463F-976E-28099628842D
3               |   EmployeeType|FullTime          |    BF3DAC05-8131-463F-976E-28099628842D
3               |   Language|Spanish               |    BF3DAC05-8131-463F-976E-28099628842D

GroupId值内的每个唯一OperatorGroup代表与特定组匹配的完整规则集。这样，我就可以透视我的员工表和组表，并进行联接以查找匹配的组。

我在下面粘贴了架构数据和查询。

架构图

CREATE TABLE [dbo].[Employee]([EmployeeId] [int], [EmployerId] [int] NOT NULL, [Name] [nvarchar](50) NOT NULL)
CREATE TABLE [dbo].[EmployeeTag]([EmployeeTagId] [int], [EmployeeId] [int] NOT NULL, [TagId] [int] NOT NULL)
CREATE TABLE [dbo].[Employer]([EmployerId] [int], [Name] [nvarchar](50) NOT NULL)
CREATE TABLE [dbo].[Group]([GroupId] [int], [EmployerId] [int] NOT NULL, [Name] [nvarchar](50) NOT NULL)
CREATE TABLE [dbo].[GroupDefinition]([GroupDefinitionId] [int], [GroupId] [int] NOT NULL, [TagId] [int] NOT NULL, [Grouping] [uniqueidentifier] NOT NULL)
CREATE TABLE [dbo].[Tag]([TagId] [int], [Name] [nvarchar](50) NOT NULL, [Value] [nvarchar](50) NOT NULL)

数据

INSERT [dbo].[Employer] ([EmployerId], [Name]) VALUES (1, N'Fabrikam')

INSERT [dbo].[Employee] ([EmployeeId], [EmployerId], [Name]) VALUES (1, 1, N'John Smith')
INSERT [dbo].[Employee] ([EmployeeId], [EmployerId], [Name]) VALUES (2, 1, N'Jane Miller')
INSERT [dbo].[Employee] ([EmployeeId], [EmployerId], [Name]) VALUES (3, 1, N'Nelda Maughan')
INSERT [dbo].[Employee] ([EmployeeId], [EmployerId], [Name]) VALUES (4, 1, N'Joe Anderson')
INSERT [dbo].[Employee] ([EmployeeId], [EmployerId], [Name]) VALUES (5, 1, N'Ami Mcmanis')
INSERT [dbo].[Employee] ([EmployeeId], [EmployerId], [Name]) VALUES (6, 1, N'Charlena Ashbaugh')
INSERT [dbo].[Employee] ([EmployeeId], [EmployerId], [Name]) VALUES (7, 1, N'Matt Tutino')
INSERT [dbo].[Employee] ([EmployeeId], [EmployerId], [Name]) VALUES (8, 1, N'Sulema Alexandria')
INSERT [dbo].[Employee] ([EmployeeId], [EmployerId], [Name]) VALUES (9, 1, N'Ema Wolak')
INSERT [dbo].[Employee] ([EmployeeId], [EmployerId], [Name]) VALUES (10, 1, N'Marinda Krejci')
INSERT [dbo].[Employee] ([EmployeeId], [EmployerId], [Name]) VALUES (11, 1, N'Felipa Chew')
INSERT [dbo].[Employee] ([EmployeeId], [EmployerId], [Name]) VALUES (12, 1, N'Armand Hosier')
INSERT [dbo].[Employee] ([EmployeeId], [EmployerId], [Name]) VALUES (13, 1, N'Roland Blunk')
INSERT [dbo].[Employee] ([EmployeeId], [EmployerId], [Name]) VALUES (14, 1, N'Ellen Frasier')
INSERT [dbo].[Employee] ([EmployeeId], [EmployerId], [Name]) VALUES (15, 1, N'Nicolle Tarbell')
INSERT [dbo].[Employee] ([EmployeeId], [EmployerId], [Name]) VALUES (16, 1, N'Christopher Lautenschlage')
INSERT [dbo].[Employee] ([EmployeeId], [EmployerId], [Name]) VALUES (17, 1, N'Nelson Maughan')
INSERT [dbo].[Employee] ([EmployeeId], [EmployerId], [Name]) VALUES (18, 1, N'Wes Boyd')
INSERT [dbo].[Employee] ([EmployeeId], [EmployerId], [Name]) VALUES (19, 1, N'Jason Lessley')
INSERT [dbo].[Employee] ([EmployeeId], [EmployerId], [Name]) VALUES (20, 1, N'Jesus Purves')

INSERT [dbo].[Tag] ([TagId], [Name], [Value]) VALUES (1, N'Country', N'United States')
INSERT [dbo].[Tag] ([TagId], [Name], [Value]) VALUES (2, N'Country', N'Canada')
INSERT [dbo].[Tag] ([TagId], [Name], [Value]) VALUES (3, N'Country', N'Brazil')
INSERT [dbo].[Tag] ([TagId], [Name], [Value]) VALUES (4, N'EmployeeType', N'Full Time')
INSERT [dbo].[Tag] ([TagId], [Name], [Value]) VALUES (5, N'EmployeeType', N'Part Time')
INSERT [dbo].[Tag] ([TagId], [Name], [Value]) VALUES (6, N'Language', N'Spanish')
INSERT [dbo].[Tag] ([TagId], [Name], [Value]) VALUES (7, N'Language', N'French')
INSERT [dbo].[Tag] ([TagId], [Name], [Value]) VALUES (8, N'Language', N'English')

INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (1, 1, 1)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (2, 1, 4)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (3, 1, 6)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (4, 2, 2)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (5, 2, 5)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (6, 2, 6)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (7, 3, 2)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (8, 3, 4)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (9, 3, 7)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (10, 4, 3)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (11, 4, 4)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (12, 4, 7)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (13, 5, 2)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (14, 5, 4)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (15, 5, 6)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (16, 6, 1)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (17, 6, 6)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (18, 7, 1)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (19, 7, 5)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (20, 7, 7)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (21, 8, 3)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (22, 8, 4)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (23, 9, 1)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (24, 9, 4)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (25, 9, 7)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (26, 10, 3)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (27, 10, 6)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (28, 11, 1)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (29, 11, 4)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (30, 11, 8)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (31, 12, 1)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (32, 12, 5)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (33, 12, 6)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (34, 13, 1)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (35, 13, 5)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (36, 13, 8)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (37, 14, 2)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (38, 14, 4)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (39, 14, 8)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (40, 15, 2)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (41, 15, 5)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (42, 15, 8)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (43, 16, 3)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (44, 16, 8)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (45, 17, 1)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (46, 17, 8)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (47, 18, 8)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (48, 19, 2)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (49, 19, 7)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (50, 20, 3)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (51, 20, 4)
INSERT [dbo].[EmployeeTag] ([EmployeeTagId], [EmployeeId], [TagId]) VALUES (52, 20, 6)

INSERT [dbo].[Group] ([GroupId], [EmployerId], [Name]) VALUES (1, 1, N'US Full Timers')
INSERT [dbo].[Group] ([GroupId], [EmployerId], [Name]) VALUES (2, 1, N'CA Full Timers')
INSERT [dbo].[Group] ([GroupId], [EmployerId], [Name]) VALUES (3, 1, N'NA Spanish Full Timers')
INSERT [dbo].[Group] ([GroupId], [EmployerId], [Name]) VALUES (4, 1, N'NA French Full Timers')
INSERT [dbo].[Group] ([GroupId], [EmployerId], [Name]) VALUES (5, 1, N'US Part Timers')
INSERT [dbo].[Group] ([GroupId], [EmployerId], [Name]) VALUES (6, 1, N'CA Part Timers')
INSERT [dbo].[Group] ([GroupId], [EmployerId], [Name]) VALUES (7, 1, N'NA Spanish Part Timers')
INSERT [dbo].[Group] ([GroupId], [EmployerId], [Name]) VALUES (8, 1, N'NA French Part Timers')
INSERT [dbo].[Group] ([GroupId], [EmployerId], [Name]) VALUES (9, 1, N'BR Spanish Full Timers')
INSERT [dbo].[Group] ([GroupId], [EmployerId], [Name]) VALUES (10, 1, N'Spanish Speakers')
INSERT [dbo].[Group] ([GroupId], [EmployerId], [Name]) VALUES (11, 1, N'US Employees')
INSERT [dbo].[Group] ([GroupId], [EmployerId], [Name]) VALUES (12, 1, N'BR Employees')

INSERT [dbo].[GroupDefinition] ([GroupDefinitionId], [GroupId], [TagId], [Grouping]) VALUES (1, 1, 1, N'8e336244-94ac-4e62-a719-0a4a0b01a6f2')
INSERT [dbo].[GroupDefinition] ([GroupDefinitionId], [GroupId], [TagId], [Grouping]) VALUES (2, 1, 4, N'8e336244-94ac-4e62-a719-0a4a0b01a6f2')
INSERT [dbo].[GroupDefinition] ([GroupDefinitionId], [GroupId], [TagId], [Grouping]) VALUES (3, 2, 2, N'b3c87fe8-7b9f-4648-9708-20a460cf9314')
INSERT [dbo].[GroupDefinition] ([GroupDefinitionId], [GroupId], [TagId], [Grouping]) VALUES (4, 2, 4, N'b3c87fe8-7b9f-4648-9708-20a460cf9314')
INSERT [dbo].[GroupDefinition] ([GroupDefinitionId], [GroupId], [TagId], [Grouping]) VALUES (5, 3, 1, N'36fa7924-fa11-4772-b632-84c79b6bbb1b')
INSERT [dbo].[GroupDefinition] ([GroupDefinitionId], [GroupId], [TagId], [Grouping]) VALUES (6, 3, 4, N'36fa7924-fa11-4772-b632-84c79b6bbb1b')
INSERT [dbo].[GroupDefinition] ([GroupDefinitionId], [GroupId], [TagId], [Grouping]) VALUES (7, 3, 6, N'36fa7924-fa11-4772-b632-84c79b6bbb1b')
INSERT [dbo].[GroupDefinition] ([GroupDefinitionId], [GroupId], [TagId], [Grouping]) VALUES (8, 3, 2, N'dfa7f0f9-aa7a-401b-8bfd-804aeadff1a9')
INSERT [dbo].[GroupDefinition] ([GroupDefinitionId], [GroupId], [TagId], [Grouping]) VALUES (9, 3, 4, N'dfa7f0f9-aa7a-401b-8bfd-804aeadff1a9')
INSERT [dbo].[GroupDefinition] ([GroupDefinitionId], [GroupId], [TagId], [Grouping]) VALUES (10, 3, 6, N'dfa7f0f9-aa7a-401b-8bfd-804aeadff1a9')
INSERT [dbo].[GroupDefinition] ([GroupDefinitionId], [GroupId], [TagId], [Grouping]) VALUES (11, 4, 1, N'b857e999-0a7e-49fe-b138-88aed2d81aa3')
INSERT [dbo].[GroupDefinition] ([GroupDefinitionId], [GroupId], [TagId], [Grouping]) VALUES (12, 4, 4, N'b857e999-0a7e-49fe-b138-88aed2d81aa3')
INSERT [dbo].[GroupDefinition] ([GroupDefinitionId], [GroupId], [TagId], [Grouping]) VALUES (13, 4, 7, N'b857e999-0a7e-49fe-b138-88aed2d81aa3')
INSERT [dbo].[GroupDefinition] ([GroupDefinitionId], [GroupId], [TagId], [Grouping]) VALUES (14, 4, 2, N'296741c2-de6f-46ca-adcc-5b552fb4b0eb')
INSERT [dbo].[GroupDefinition] ([GroupDefinitionId], [GroupId], [TagId], [Grouping]) VALUES (15, 4, 4, N'296741c2-de6f-46ca-adcc-5b552fb4b0eb')
INSERT [dbo].[GroupDefinition] ([GroupDefinitionId], [GroupId], [TagId], [Grouping]) VALUES (16, 4, 7, N'296741c2-de6f-46ca-adcc-5b552fb4b0eb')
INSERT [dbo].[GroupDefinition] ([GroupDefinitionId], [GroupId], [TagId], [Grouping]) VALUES (17, 5, 1, N'48fd52d4-141a-43d3-8dea-69b9df6337ed')
INSERT [dbo].[GroupDefinition] ([GroupDefinitionId], [GroupId], [TagId], [Grouping]) VALUES (18, 5, 5, N'48fd52d4-141a-43d3-8dea-69b9df6337ed')
INSERT [dbo].[GroupDefinition] ([GroupDefinitionId], [GroupId], [TagId], [Grouping]) VALUES (19, 6, 2, N'e85ba82a-0ca7-410b-ba35-726bd86a8621')
INSERT [dbo].[GroupDefinition] ([GroupDefinitionId], [GroupId], [TagId], [Grouping]) VALUES (20, 6, 5, N'e85ba82a-0ca7-410b-ba35-726bd86a8621')
INSERT [dbo].[GroupDefinition] ([GroupDefinitionId], [GroupId], [TagId], [Grouping]) VALUES (21, 7, 1, N'bb656fc1-8ccd-4ff6-85ec-185216b55e9f')
INSERT [dbo].[GroupDefinition] ([GroupDefinitionId], [GroupId], [TagId], [Grouping]) VALUES (22, 7, 5, N'bb656fc1-8ccd-4ff6-85ec-185216b55e9f')
INSERT [dbo].[GroupDefinition] ([GroupDefinitionId], [GroupId], [TagId], [Grouping]) VALUES (23, 7, 6, N'bb656fc1-8ccd-4ff6-85ec-185216b55e9f')
INSERT [dbo].[GroupDefinition] ([GroupDefinitionId], [GroupId], [TagId], [Grouping]) VALUES (24, 7, 2, N'8333dc24-4850-40d9-8b34-86c5d33bca60')
INSERT [dbo].[GroupDefinition] ([GroupDefinitionId], [GroupId], [TagId], [Grouping]) VALUES (25, 7, 5, N'8333dc24-4850-40d9-8b34-86c5d33bca60')
INSERT [dbo].[GroupDefinition] ([GroupDefinitionId], [GroupId], [TagId], [Grouping]) VALUES (26, 7, 6, N'8333dc24-4850-40d9-8b34-86c5d33bca60')
INSERT [dbo].[GroupDefinition] ([GroupDefinitionId], [GroupId], [TagId], [Grouping]) VALUES (27, 8, 1, N'e060b0e9-759f-4fe5-80d8-3fb2a73df6c8')
INSERT [dbo].[GroupDefinition] ([GroupDefinitionId], [GroupId], [TagId], [Grouping]) VALUES (28, 8, 5, N'e060b0e9-759f-4fe5-80d8-3fb2a73df6c8')
INSERT [dbo].[GroupDefinition] ([GroupDefinitionId], [GroupId], [TagId], [Grouping]) VALUES (29, 8, 7, N'e060b0e9-759f-4fe5-80d8-3fb2a73df6c8')
INSERT [dbo].[GroupDefinition] ([GroupDefinitionId], [GroupId], [TagId], [Grouping]) VALUES (30, 8, 2, N'33854572-3081-4e1c-ae98-fa15b2bc86b3')
INSERT [dbo].[GroupDefinition] ([GroupDefinitionId], [GroupId], [TagId], [Grouping]) VALUES (31, 8, 5, N'33854572-3081-4e1c-ae98-fa15b2bc86b3')
INSERT [dbo].[GroupDefinition] ([GroupDefinitionId], [GroupId], [TagId], [Grouping]) VALUES (32, 8, 7, N'33854572-3081-4e1c-ae98-fa15b2bc86b3')
INSERT [dbo].[GroupDefinition] ([GroupDefinitionId], [GroupId], [TagId], [Grouping]) VALUES (33, 9, 3, N'a4bfd6b8-556a-4e01-b3e8-b725a85c458c')
INSERT [dbo].[GroupDefinition] ([GroupDefinitionId], [GroupId], [TagId], [Grouping]) VALUES (34, 9, 4, N'a4bfd6b8-556a-4e01-b3e8-b725a85c458c')
INSERT [dbo].[GroupDefinition] ([GroupDefinitionId], [GroupId], [TagId], [Grouping]) VALUES (35, 9, 6, N'a4bfd6b8-556a-4e01-b3e8-b725a85c458c')
INSERT [dbo].[GroupDefinition] ([GroupDefinitionId], [GroupId], [TagId], [Grouping]) VALUES (36, 10, 6, N'649c0f7a-a5d3-4713-9335-8be57137347e')
INSERT [dbo].[GroupDefinition] ([GroupDefinitionId], [GroupId], [TagId], [Grouping]) VALUES (37, 11, 1, N'54c43caa-67a6-4be3-b237-2a89423360c8')
INSERT [dbo].[GroupDefinition] ([GroupDefinitionId], [GroupId], [TagId], [Grouping]) VALUES (38, 12, 3, N'd282d453-9671-408e-b6c6-d36296369a2e')

询问

DECLARE @employerId INT = 1

IF OBJECT_ID('tempdb..#pivotedEmployeeTags') IS NOT NULL DROP TABLE #pivotedEmployeeTags;
IF OBJECT_ID('tempdb..#pivotedGroupDefinition') IS NOT NULL DROP TABLE #pivotedGroupDefinition;

CREATE TABLE #pivotedEmployeeTags (EmployeeId INT, Name NVARCHAR(50), Country INT NULL, EmployeeType INT NULL, [Language] INT NULL)
CREATE TABLE #pivotedGroupDefinition (GroupId INT, Name NVARCHAR(50), Country INT NULL, EmployeeType INT NULL, [Language] INT NULL)

;WITH FlattenedEmployeeTags AS
(
    SELECT 
        Employee.EmployeeId
        ,Employee.Name
        ,Tag.TagId
        ,Tag.Name as TagName
    FROM Employee
        LEFT JOIN EmployeeTag ON EmployeeTag.EmployeeId = Employee.EmployeeId
        LEFT JOIN Tag ON Tag.TagId = EmployeeTag.TagId
    WHERE EmployerId = @employerId
)
INSERT INTO #pivotedEmployeeTags
    SELECT 
        EmployeeId
        ,Name
        ,Country = MAX( CASE WHEN TagName = 'Country' THEN TagId END )
        ,EmployeeType = MAX( CASE WHEN TagName = 'EmployeeType' THEN TagId END )
        ,[Language] = MAX( CASE WHEN TagName = 'Language' THEN TagId END )
    FROM FlattenedEmployeeTags
    GROUP BY EmployeeId, Name

;WITH FlattenedGroupDefinition AS
(
    SELECT 
        [Group].GroupId
        ,[Group].Name
        ,Tag.TagId
        ,Tag.Name as TagName
        ,Grouping
    FROM [Group]
        LEFT JOIN GroupDefinition ON GroupDefinition.GroupId = [Group].GroupId
        LEFT JOIN Tag ON Tag.TagId = GroupDefinition.TagId
    WHERE EmployerId = @employerId
)
INSERT INTO #pivotedGroupDefinition
    SELECT 
        GroupId 
        ,Name
        ,Country = MAX( CASE WHEN TagName = 'Country' THEN TagId END )
        ,EmployeeType = MAX( CASE WHEN TagName = 'EmployeeType' THEN TagId END )
        ,[Language] = MAX( CASE WHEN TagName = 'Language' THEN TagId END )
    FROM FlattenedGroupDefinition
    GROUP BY GroupId, Name, Grouping
    ORDER BY GroupId, Grouping

SELECT 
    EmpTags.EmployeeId
    , GrpTags.GroupId
FROM 
    #pivotedEmployeeTags EmpTags
    INNER JOIN #pivotedGroupDefinition GrpTags 
        ON 
            (GrpTags.Country = EmpTags.Country OR GrpTags.Country IS NULL)      
            AND (GrpTags.EmployeeType = EmpTags.EmployeeType OR GrpTags.EmployeeType IS NULL)           
            AND (GrpTags.[Language] = EmpTags.[Language] OR GrpTags.[Language] IS NULL)
ORDER BY EmpTags.EmployeeId, GrpTags.GroupId

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。