SQL SELECT其中标记值为LIKE

我正在尝试制作日历服务,在该日历服务中,有事件,并且事件可以使用可搜索的元数据进行标记.

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

当标签值与’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匹配的建议吗？

非常感谢

解决方法

小提琴：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 [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匹配.希望能帮助到你！

（编辑：安卓应用网）

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