如何在T-SQL中实现带AND逻辑的IN子句多值匹配

核心需求分析 #

需要从tbl_Values中找出同时关联所有指定MatchValue的记录，而非关联任一指定值的记录（IN子句的默认行为）。比如传入'1.1,3.1'时，仅返回同时关联这两个值的test1、test3，排除只关联其中一个的test2。

可行解决方案 #

方案1：拆分CSV后分组统计匹配数（推荐，SQL Server 2016+） #

利用STRING_SPLIT拆分CSV字符串，通过分组统计匹配的唯一值数量，判断是否等于传入的条件总数：

DECLARE @CSV NVARCHAR(MAX) = '1.1,3.1';
-- 统计传入的匹配值数量
DECLARE @RequiredMatchCount INT = (SELECT COUNT(*) FROM STRING_SPLIT(@CSV, ','));

SELECT 
    v.ValueID, 
    v.ValueName
FROM tbl_Values v
JOIN tbl_Values_MatchValues vm ON v.ValueID = vm.ValueID
JOIN tbl_MatchValues mv ON vm.MatchValueID = mv.MatchValueID
-- 匹配传入的所有值
WHERE mv.MatchValue IN (SELECT value FROM STRING_SPLIT(@CSV, ','))
GROUP BY v.ValueID, v.ValueName
-- 确保当前Value关联了所有传入的匹配值（去重避免重复关联的干扰）
HAVING COUNT(DISTINCT mv.MatchValue) = @RequiredMatchCount;

方案2：临时表辅助匹配（你的现有方案） #

这并非不良实践，适合需要复用拆分结果或CSV内容较长的场景，优化后性能表现稳定：

DECLARE @CSV NVARCHAR(MAX) = '1.1,3.1';

-- 创建临时表存储拆分后的匹配值，加主键索引优化JOIN性能
CREATE TABLE #TempMatches (MatchValue NVARCHAR(50) PRIMARY KEY);
INSERT INTO #TempMatches (MatchValue)
SELECT value FROM STRING_SPLIT(@CSV, ',');

DECLARE @RequiredMatchCount INT = (SELECT COUNT(*) FROM #TempMatches);

SELECT 
    v.ValueID, 
    v.ValueName
FROM tbl_Values v
JOIN tbl_Values_MatchValues vm ON v.ValueID = vm.ValueID
JOIN tbl_MatchValues mv ON vm.MatchValueID = mv.MatchValueID
JOIN #TempMatches tm ON mv.MatchValue = tm.MatchValue
GROUP BY v.ValueID, v.ValueName
HAVING COUNT(DISTINCT mv.MatchValue) = @RequiredMatchCount;

DROP TABLE #TempMatches;

方案3：双层EXISTS子查询（逻辑直观） #

通过嵌套EXISTS判断：不存在任何一个传入的匹配值未被当前Value关联，即所有值都匹配：

DECLARE @CSV NVARCHAR(MAX) = '1.1,3.1';

DECLARE @Matches TABLE (MatchValue NVARCHAR(50));
INSERT INTO @Matches (MatchValue)
SELECT value FROM STRING_SPLIT(@CSV, ',');

SELECT 
    v.ValueID, 
    v.ValueName
FROM tbl_Values v
WHERE NOT EXISTS (
    -- 检查是否有传入的匹配值未被当前Value关联
    SELECT 1 FROM @Matches tm
    WHERE NOT EXISTS (
        SELECT 1 FROM tbl_Values_MatchValues vm
        JOIN tbl_MatchValues mv ON vm.MatchValueID = mv.MatchValueID
        WHERE vm.ValueID = v.ValueID AND mv.MatchValue = tm.MatchValue
    )
);

临时表方案的性能与实践分析 #

1. 是否为不良实践？
   完全不是。临时表是T-SQL中处理中间结果的常用手段，尤其在复杂查询或需要复用拆分结果时，比重复拆分CSV更高效。

2. 大数据量下的性能注意事项

   • 给临时表加索引：如果传入的CSV包含大量值，给#TempMatches的MatchValue列加主键或非聚集索引，能大幅提升JOIN的速度。
   • 优化关联表索引：确保tbl_Values_MatchValues有(ValueID, MatchValueID)的复合索引，tbl_MatchValues有(MatchValue, MatchValueID)的索引，减少关联时的查找开销。
   • 分组统计的效率：分组统计的逻辑本身是高效的，SQL Server查询优化器会利用索引进行快速分组和计数，只要索引合理，大数据量下也能稳定运行。

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