我希望能够搜索所有标记必须存在的记录(强制标记)和/或存在任何标记的位置(可选标记).

当标签值与’exact’匹配时,我设法创建了一个可以工作的查询.但我无法弄清楚如何返回标签值为LIKE’％value％’的结果.

这是我目前的实施

表和数据

CREATE TABLE Events
(
 Id INT,EventText VARCHAR(500)
);
 
CREATE TABLE EventDates
(
 Id INT,EventId INT,StartDate DATETIME,EndDate DATETIME,Archived BIT
);
 
CREATE TABLE Tags
(
 Id INT,Description VARCHAR(50)
);
 
CREATE TABLE EventTags
(
 EventId INT,TagId INT,Value VARCHAR(50)
);
 
INSERT INTO Events VALUES (1,'Event Name 1');
INSERT INTO Events VALUES (2,'Event Name 2');
 
INSERT INTO EventDates VALUES (1,1,'2013-01-01','2013-01-02',0);
INSERT INTO EventDates VALUES (2,'2013-01-07','2013-01-08',0);
INSERT INTO EventDates VALUES (3,2,'2013-01-03',0);
 
INSERT INTO Tags VALUES (1,'Tag Name 1');
INSERT INTO Tags VALUES (2,'Tag Name 2');
 
INSERT INTO EventTags VALUES (1,'Value 1');
INSERT INTO EventTags VALUES (1,'Value 2');
INSERT INTO EventTags VALUES (1,'Value 2');
INSERT INTO EventTags VALUES (2,'Value 1');

询问

DECLARE @MandatoryTagXml XML
DECLARE @OptionalTagXml XML
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @SearchTypeId SMALLINT
 
SET @StartDate = '2013-01-01'
SET @EndDate = '2013-01-31'
SET @SearchTypeId = 1
 
-- Tags that it must match all of
SET @MandatoryTagXml = '<tags>
                          <tag>
                            <description>Tag Name 1</description>
                            <value>Value 1</value>
                          </tag>
                        </tags>'
 
-- Tags that it can match one or more of
SET @OptionalTagXml = '<tags>
                          <tag>
                            <description>Tag Name 2</description>
                            <value>Value 2</value>
                          </tag>
                        </tags>'
 
    DECLARE @MandatoryIdTable TABLE ([EventId] BIGINT,[EventDateId] BIGINT)
    DECLARE @OptionalIdTable TABLE ([EventId] BIGINT,[EventDateId] BIGINT)
 
IF(@MandatoryTagXml IS NOT NULL)
BEGIN
    -- Select ids with matching mandatory 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)
      )
 
    INSERT INTO @MandatoryIdTable
    -- Records where ALL tags match EXACTLY
    SELECT E.Id [EventId],ED.Id [EventDateId]
    FROM [dbo].[Events] E
    INNER JOIN [dbo].[EventDates] ED ON ED.EventId = E.Id
    WHERE ED.StartDate >= @StartDate
    AND ED.EndDate <= @EndDate
    AND ED.Archived = 0
    AND NOT EXISTS (
                    SELECT T.Id,c.value
                    FROM MandatoryTags c JOIN Tags T
                        ON c.[description] = T.[Description]
                    EXCEPT
                    SELECT T.TagId,T.Value
                    FROM [EventTags] T
                    WHERE T.EventId = E.Id                          
                    )
END
ELSE -- Select All records 
BEGIN
    INSERT INTO @MandatoryIdTable
    -- Records where ALL tags match EXACTLY
    SELECT E.Id [EventId],ED.Id [EventDateId]
    FROM [dbo].[Events] E
    INNER JOIN [dbo].[EventDates] ED ON ED.EventId = E.Id
    WHERE ED.StartDate >= @StartDate
    AND ED.EndDate <= @EndDate
    AND ED.Archived = 0
END
 
    ;WITH OptionalTags AS
     (
      SELECT TagValue.value('(./value)[1]','nvarchar(100)') AS [description]       
      FROM @OptionalTagXml.nodes('/tags/tag') AS T(TagValue)
      )
 
    INSERT INTO @OptionalIdTable
    -- Records ANY tags match EXACTLY
    SELECT E.Id [EventId],ED.Id [EventDateId]
    FROM [dbo].[Events] E
    INNER JOIN [dbo].[EventDates] ED ON ED.EventId = E.Id
    WHERE ED.StartDate >= @StartDate
    AND ED.EndDate <= @EndDate
    AND ED.Archived = 0
    AND EXISTS (
                SELECT T.Id,c.value
                FROM OptionalTags c JOIN Tags T
                    ON c.[description] = T.[Description]
                INTERSECT
                SELECT T.TagId,T.Value
                FROM [EventTags] T
                WHERE T.EventId = E.Id                          
                )
 
-- Determine if we need to factor in optional tags in result set
IF (@OptionalTagXml IS NOT NULL)
BEGIN
    -- Select results that exist in both optional and mandatory tables
    SELECT DISTINCT M.*
    FROM @MandatoryIdTable M
    INNER JOIN @OptionalIdTable O ON O.EventId = M.EventId AND O.EventDateId = M.EventDateId
END
ELSE
BEGIN
    -- Select results that exist in mandatory table
    SELECT DISTINCT M.*
    FROM @MandatoryIdTable M
END

我为它创建了一个SQLFiddle Demo.

我的想法是使用@SearchTypeId在完全匹配搜索和LIKE匹配搜索之间切换.

(注意我不是DBA,所以可能有更好的方法来做到这一点.我愿意接受建议)

任何人都可以提供关于如何在标签值上获得LIKE匹配的建议吗？

非常感谢