MSSQL中基于标签集合的BlogPost查询实现（含Dapper场景）

首先明确常规数据库设计方案（比存标签ID数组更高效易维护）：

• BlogPosts：业务表，主键blogId，包含博客其他字段
• BlogPostTags：标签关联表，字段blogId（外键关联BlogPosts）、tagId，建议创建(tagId, blogId)联合索引提升查询效率

1. 查询恰好包含指定标签的BlogPost（仅含标签1、2，无其他标签） #

SQL 实现 #

核心逻辑：先筛选出含目标标签的博客，再通过分组统计+不存在非目标标签的校验，确保标签集合完全匹配

SELECT bp.blogId
FROM BlogPosts bp
JOIN BlogPostTags bpt ON bp.blogId = bpt.blogId
WHERE bpt.tagId IN (@TagIds)
GROUP BY bp.blogId
HAVING COUNT(DISTINCT bpt.tagId) = @TagCount
AND NOT EXISTS (
    SELECT 1
    FROM BlogPostTags bpt2
    WHERE bpt2.blogId = bp.blogId
    AND bpt2.tagId NOT IN (@TagIds)
)

Dapper 适配写法 #

Dapper原生支持数组参数传入，直接映射SQL中的@TagIds和@TagCount：

var targetTags = new int[] {1, 2};
var tagCount = targetTags.Length;

var matchedBlogs = connection.Query<int>(@"
    SELECT bp.blogId
    FROM BlogPosts bp
    JOIN BlogPostTags bpt ON bp.blogId = bpt.blogId
    WHERE bpt.tagId IN @TagIds
    GROUP BY bp.blogId
    HAVING COUNT(DISTINCT bpt.tagId) = @TagCount
    AND NOT EXISTS (
        SELECT 1
        FROM BlogPostTags bpt2
        WHERE bpt2.blogId = bp.blogId
        AND bpt2.tagId NOT IN @TagIds
    )",
    new { TagIds = targetTags, TagCount = tagCount });

2. 查询包含所有指定标签的BlogPost（至少含标签1、2，可额外包含其他标签） #

SQL 实现 #

核心逻辑：筛选出含目标标签的博客，分组后统计匹配的标签数量等于目标标签总数，确保每个目标标签都被包含

SELECT bp.blogId
FROM BlogPosts bp
JOIN BlogPostTags bpt ON bp.blogId = bpt.blogId
WHERE bpt.tagId IN (@TagIds)
GROUP BY bp.blogId
HAVING COUNT(DISTINCT bpt.tagId) = @TagCount

Dapper 适配写法 #

同样利用Dapper数组参数特性，代码简洁高效：

var targetTags = new int[] {1, 2};
var tagCount = targetTags.Length;

var matchedBlogs = connection.Query<int>(@"
    SELECT bp.blogId
    FROM BlogPosts bp
    JOIN BlogPostTags bpt ON bp.blogId = bpt.blogId
    WHERE bpt.tagId IN @TagIds
    GROUP BY bp.blogId
    HAVING COUNT(DISTINCT bpt.tagId) = @TagCount",
    new { TagIds = targetTags, TagCount = tagCount });

补充：若标签ID以JSON数组存储在BlogPosts表中 #

如果业务场景必须用JSON数组存储标签（如TagIds字段存[1,2,3]），可使用MSSQL JSON函数实现：

恰好包含指定标签 #

SELECT blogId
FROM BlogPosts
WHERE (SELECT COUNT(*) FROM OPENJSON(TagIds) WITH (tagId int '$')) = @TagCount
AND NOT EXISTS (
    SELECT 1 FROM OPENJSON(TagIds) WITH (tagId int '$')
    WHERE tagId NOT IN (@TagIds)
)
AND EXISTS (
    SELECT 1 FROM OPENJSON(@TargetTagsJson) WITH (tagId int '$') t
    WHERE EXISTS (SELECT 1 FROM OPENJSON(TagIds) WITH (tagId int '$') b WHERE b.tagId = t.tagId)
)

包含所有指定标签 #

SELECT blogId
FROM BlogPosts
WHERE (SELECT COUNT(DISTINCT tagId) FROM OPENJSON(TagIds) WITH (tagId int '$') WHERE tagId IN (@TagIds)) = @TagCount

Dapper写法只需额外传入序列化后的JSON字符串参数，逻辑与关联表版本一致。

内容的提问来源于stack exchange，提问作者user3116167