sql - SQL Server 中存在 WHERE EXISTS 的 JOIN ON LIKE 问题

Question

我正在尝试让标签搜索正常工作，除了 LIKE 标签匹配之外，它主要工作。

我已将示例代码添加到 SQLFiddle可以玩，也包含在这里:

表格和数据

CREATE TABLE Attendees
(
 Id INT,
 Text VARCHAR(500)
);


CREATE TABLE Tags
(
 Id INT,
 Description VARCHAR(50)
);

CREATE TABLE AttendeeTags
(
 AttendeeId INT,
 TagId INT,
 Value VARCHAR(50)
);

INSERT INTO Attendees VALUES (1, 'Attendee 1');
INSERT INTO Attendees VALUES (2, 'Attendee 2');

INSERT INTO Tags VALUES (1, 'Tag Name 1');
INSERT INTO Tags VALUES (2, 'Tag Name 2');

INSERT INTO AttendeeTags VALUES (1, 1, 'Value 1');
INSERT INTO AttendeeTags VALUES (1, 1, 'Value 2');
INSERT INTO AttendeeTags VALUES (1, 2, 'Value 1');
INSERT INTO AttendeeTags VALUES (1, 2, 'Value 2');
INSERT INTO AttendeeTags VALUES (2, 1, 'Value 1');

查询

DECLARE @MandatoryTagXml XML 

SET @MandatoryTagXml = '<tags><tag><description>Tag Name 1</description><value>Value 2</value></tag></tags>'

;WITH MandatoryTags AS
         (
          SELECT TagValue.value('(./value)[1]', 'nvarchar(100)') AS value,
                 TagValue.value('(./description)[1]', 'nvarchar(100)') AS [description]       
          FROM @MandatoryTagXml.nodes('/tags/tag') AS T(TagValue)
          )

SELECT DISTINCT A.Id [AttendeeId]
        FROM [dbo].[Attendees] A
        INNER JOIN [dbo].[AttendeeTags] AT ON AT.AttendeeId = AttendeeId
        INNER JOIN [dbo].[Tags] T ON T.Id = AT.TagId AND T.[Description] IN (SELECT [description] FROM MandatoryTags)
        WHERE NOT EXISTS (
                        SELECT T.Id, c.value
                        FROM MandatoryTags c 
                        JOIN Tags T
                            ON c.[description] = T.[Description]
                        -- Add LIKE match to value - This is the problem line
                        JOIN AttendeeTags AT
                            ON AT.Value LIKE '%' + C.[Value] + '%'
                        EXCEPT
                        SELECT ATT.TagId, ATT.Value
                        FROM [AttendeeTags] ATT
                        WHERE ATT.AttendeeId = A.Id                          
                        )

我想要的是当 Tags.Description 与 @MandatoryTagXml 中的描述完全匹配，并且 attendeeTags.Value 与 @MandatoryTagXml 中提供的值类似时获得结果

如果没有以下行，事情会按预期工作(即，当 XML 上存在完全匹配时)

JOIN AttendeeTags AT ON AT.Value LIKE '%' + C.[Value] + '%'

但是当我包含它时，我开始得到不正确的结果。例如，设置 Value 应返回 attendee.Id，但不会返回任何结果。

我尝试过 EXISTS 和 NOT EXISTS、EXCEPT 和 INTERSECT 等的各种组合，但无法让它在所有情况下都起作用。

任何人都可以提供有关如何使其发挥作用的任何建议吗？

Answer 1

我认为您的加入比需要的更复杂......

DECLARE @MandatoryTagXml XML 

SET @MandatoryTagXml = '
<tags>
<tag><description>Tag Name 1</description><value>Value 1</value></tag>
<tag><description>Tag Name 2</description><value>Value 2</value></tag>
</tags>'

;WITH MandatoryTags AS
         (
          SELECT TagValue.value('(./value)[1]', 'nvarchar(100)') AS value,
                 TagValue.value('(./description)[1]', 'nvarchar(100)') AS [description]       
          FROM @MandatoryTagXml.nodes('/tags/tag') AS T(TagValue)
          )

SELECT A.Id [AttendeeId]
FROM [dbo].[Attendees] A
  INNER JOIN [dbo].[AttendeeTags] AT
    INNER JOIN [dbo].[Tags] T
    ON T.Id = AT.TagId
  ON AT.AttendeeId = A.Id

  INNER JOIN MandatoryTags m
  ON T.Description = m.Description
  AND AT.Value LIKE ('%' + m.Value + '%')
GROUP BY A.Id
-- Make sure that all of the tags are matched
HAVING COUNT(*) = (SELECT COUNT(*) FROM MandatoryTags)

更新:我已更改 SQL 以强制匹配 xml 中的所有标记。