如何从表中筛选匹配子查询所有条件的记录？

现有两张数据表：

• QryTable：存储包含CSV格式值的记录
• CriterionTbl：存储筛选条件值

目前已实现匹配任一条件的查询，但需要修改为仅返回QryTable中同时包含所有条件值的记录。

示例表结构与测试数据 #

CREATE TABLE QryTable (
    ID [int], 
    Description [nvarchar](255), 
    CSV_Vals [nvarchar](255)
)
INSERT INTO QryTable
VALUES
    (1, 'Description of record #1', 'val_1,val_2,val_3,val_4,val_5,val_6'),
    (2, 'Description of record #2', 'val_1,val_3,val_6,val_9,val_10,val_11'),
    (3, 'Description of record #3', 'val_2,val_3,val_4,val_15,val_20,val_21')

CREATE TABLE CriterionTbl (
    ID [int] identity (1,1), 
    CriterionVals [nvarchar](50)
)
INSERT INTO CriterionTbl VALUES ('val_3'), ('val_4')

期望逻辑（静态写法参考） #

需要实现类似以下静态查询的逻辑，但条件需动态从CriterionTbl获取：

SELECT *
FROM QryTable
WHERE CSV_vals LIKE '%val_3%'
  AND CSV_vals LIKE '%val_4%'

当前实现的问题 #

现有匹配任一条件的查询会返回记录1、2、3，但我们需要仅返回同时包含val_3和val_4的记录1和3：

SELECT *
FROM QryTable
JOIN (SELECT * FROM CriterionTbl as CT)
ON QryTable.CSV_Vals LIKE '%' + CT.CriterionVals + '%'

解决方案 #

方法1：GROUP BY + HAVING 统计匹配条件数 #

通过关联匹配所有条件，再分组统计匹配的条件数量，只有数量等于总条件数的记录才符合要求：

SELECT QT.*
FROM QryTable QT
JOIN CriterionTbl CT ON QT.CSV_Vals LIKE '%' + CT.CriterionVals + '%'
GROUP BY QT.ID, QT.Description, QT.CSV_Vals
HAVING COUNT(DISTINCT CT.CriterionVals) = (SELECT COUNT(*) FROM CriterionTbl)

方法2：NOT EXISTS 反向排除不匹配记录 #

反向判断：如果没有任何一个条件不匹配当前记录，则说明所有条件都满足：

SELECT *
FROM QryTable QT
WHERE NOT EXISTS (
    SELECT 1
    FROM CriterionTbl CT
    WHERE QT.CSV_Vals NOT LIKE '%' + CT.CriterionVals + '%'
)

方法3：精确匹配（避免LIKE误匹配） #

如果使用的是支持字符串拆分的SQL方言（如SQL Server的STRING_SPLIT），可以拆分CSV值后精确匹配，避免LIKE导致的部分匹配问题（比如val_3匹配val_30）：

SELECT QT.*
FROM QryTable QT
WHERE (
    SELECT COUNT(DISTINCT CT.CriterionVals)
    FROM CriterionTbl CT
    JOIN STRING_SPLIT(QT.CSV_Vals, ',') SS ON SS.value = CT.CriterionVals
) = (SELECT COUNT(*) FROM CriterionTbl)

注意事项 #

CSV格式存储多值属于SQL反模式，会导致查询性能低下、容易出现匹配错误。长期来看，建议将CSV字段拆分为规范化的关联表（比如新增QryTableValues表，每条值对应一条记录），这样查询更高效、更准确。

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