JOIN ON LIKE问题SQL服务器中的EXERE EXISTS

我已将示例代码添加到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,'Value 2');
INSERT INTO AttendeeTags VALUES (1,2,'Value 2');
INSERT INTO AttendeeTags VALUES (2,'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等组合,但无法在所有情况下都能使用它.

谁能提供任何关于如何使其工作的建议？

解决方法

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 [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中的所有标记.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!