返回具有一组相关标记的记录

Question

我有 2 个表格、作业和Assignment_tags

ASSIGNMENTS
-----------
ASSIGNMENTID     int
ASSIGNMENTNAME   nvarchar(50)
ASSIGNMENT_TAGS
---------------
TAGID            int
ASSIGNMENTID     int
TAGNAME          nvarchar(50)

每个分配可以有多个标记。

我需要一个 SQL 查询来返回给定标签列表的分配。 我需要这些作业具有所有这些标签。

ASSIGNMENTS Table
-----------------
ASSIGNMENTID        ASSIGNMENTNAME
1                   Assignment 1
2                   Assignment 2
3                   Assignment 3
ASSIGNMENT_TAGS Table
---------------------
TAGID               ASSIGNMENTID       TAGNAME
1                   1                  Some Tag
2                   2                  Some Tag
3                   1                  Another Tag
4                   3                  A Different Tag
5                   2                  A Different Tag

如果我提供"某个标签，另一个标签"作为逗号分隔的标签列表作为存储过程的输入参数，我希望查询返回 ASSIGNMENTID 1，因为此赋值具有两个标签。

我相信这很容易，但我的大脑今天似乎没有工作。 任何帮助将不胜感激。

Answer 1

首先，

我建议使用这个有用的函数，将任意字符分隔的列表字符串转换为表。

CREATE FUNCTION [dbo].[Split]
(
@String NVARCHAR(4000),
@Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
    WITH Split(stpos,endpos)
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split
            WHERE endpos > 0
    )
    SELECT 'ID_Split' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'Data' = RTRIM(LTRIM(SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)))
    FROM Split
)
GO

有了这个函数，你可以知道哪个ASSINGMENTSID至少与列表中的一个标签无关。

DECLARE @Comma_delimited_tag_list VARCHAR(500) = 'Some Tag,Another Tag'
DECLARE @Delimiter VARCHAR(1) = ','
SELECT
        A.ASSIGNMENTID, B.*, AT.*
    FROM [dbo].[Split](@Comma_delimited_tag_list, @Delimiter) B
        FULL JOIN ASSIGNMENTS A ON 1 = 1
        LEFT JOIN ASSIGNMENT_TAGS AT ON AT.ASSIGNMENTID = A.ASSIGNMENTID AND AT.TAGNAME = B.Data
    WHERE
        AT.ASSIGNMENTID IS NULL

结果：

ASSIGNMENTID ID_Split             Data        TAGID       ASSIGNMENTID TAGNAME
------------ -------------------- ----------- ----------- ------------ ----------------
2            2                    Another Tag NULL        NULL         NULL
3            1                    Some Tag    NULL        NULL         NULL
3            2                    Another Tag NULL        NULL         NULL

终于知道了这一点，我们可以知道哪些 ASSIGNMENTID 适合嵌套以下 prev 查询的所有标签。

DECLARE @Comma_delimited_tag_list VARCHAR(500) = 'Some Tag,Another Tag'
DECLARE @Delimiter VARCHAR(1) = ','
SELECT
        A.*
    FROM ASSIGNMENTS A
        LEFT JOIN
        (
            SELECT
                    A.ASSIGNMENTID
                FROM [dbo].[Split](@Comma_delimited_tag_list, @Delimiter) B
                    FULL JOIN ASSIGNMENTS A ON 1 = 1
                    LEFT JOIN ASSIGNMENT_TAGS AT ON AT.ASSIGNMENTID = A.ASSIGNMENTID AND AT.TAGNAME = B.Data
                WHERE
                    AT.ASSIGNMENTID IS NULL
        ) B
        ON A.ASSIGNMENTID = B.ASSIGNMENTID
    WHERE
        B.ASSIGNMENTID IS NULL

ASSIGNMENTID | ASSIGNMENTNAME
------------ | --------------
1            | Assignment 1

It works for list with any @Delimiter value.

Hope it's works for you.

Answer 2

Working with comma-delimited lists is painful. But you can do it:

select att.assignmentid
from assignmenttags att
where ',' + @tags + ',' = '%,' + att.tagname + ',%'
group by att.assignmentid
having count(distinct att.tagname) = (length(@tags) - 
                                       length(replace(@tags, ',', '')
                                      ) + 1;

这假定输入列表中只有分隔值的逗号，并且列表中没有重复的值。