SQL SELECT where 标记值 LIKE

Question

我正在尝试创建一个日历服务，在该日历服务中，有事件，并且事件可以用可搜索的元数据进行标记。

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

当标签值“完全”匹配时，我已经成功地创建了一个查询。但是我无法弄清楚如何返回标记值为 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, 1, '2013-01-07', '2013-01-08', 0);
INSERT INTO EventDates VALUES (3, 2, '2013-01-02', '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, 1, 'Value 1');
INSERT INTO EventTags VALUES (1, 1, 'Value 2');
INSERT INTO EventTags VALUES (1, 2, 'Value 1');
INSERT INTO EventTags VALUES (1, 2, 'Value 2');
INSERT INTO EventTags VALUES (2, 1, '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 value,
             TagValue.value('(./description)[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 匹配的建议吗？

非常感谢

Answer 1

我认为您使用某种类型的标志/开关来更改匹配类型的想法会奏效。我使用单词而不是 ID 来实现它，但是如果您只是根据搜索类型切换连接条件，您应该按预期获得 LIKE 匹配。

fiddle :http://sqlfiddle.com/#!3/d9fbd/3/0

我首先添加了一个类似于标签 1 的标签，并将其附加到事件 2 以进行测试。

INSERT INTO Tags VALUES (3, 'Different Tag Name 1');
INSERT INTO EventTags VALUES (2, 3, 'Value 3');

然后我创建了搜索类型标志/开关。

DECLARE @SearchType NVARCHAR(10)
SET @SearchType = 'LIKE' --other type is EXACT

所以现在您可以根据该标志切换 EXISTS 连接条件。为了我的理解，我将您的 NOT EXISTS 更改为 EXISTS。下面是新的连接条件，以强制标记块为例。

    -- 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 or LIKE
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 (
                -- Just care about tag IDs here, not the values
                SELECT T.Id
                FROM MandatoryTags c JOIN Tags T
                    ON (
                      -- Toggle join type based on flag/switch
                      (@SearchType = 'EXACT' AND c.[description] = T.[Description])
                      OR
                      (@SearchType = 'LIKE' AND T.[Description] LIKE ('%' + c.[description] + '%'))
                    )
                INTERSECT
                SELECT T.TagId
                FROM [EventTags] T
                WHERE T.EventId = E.Id                          
                )

我确信您可以在此 SQL 中进行一些重构和优化，但这至少应该让您了解如何在需要时进行 LIKE 匹配。希望能帮助到你!