Oracle行构造器多列匹配IN查询在SQL Server中的等价实现方法

Oracle支持的行构造器多列IN语法无法直接在SQL Server中运行，可通过以下两种常用方案实现同等筛选逻辑：

方案1：值表关联法（推荐，适合匹配组合较多的场景） #

通过VALUES构造匹配值的临时行集，再和原表做关联筛选，可读性和执行效率更高：

SELECT t.*
FROM 你的业务表名 t
INNER JOIN (
    VALUES 
        ('pj1', 'gr1', 'pg1', 'dp1'),
        ('pj3', 'gr2', 'pg1', 'dp2'),
        ('pj6', 'gr3', 'pg2', 'dp1')
) AS filter_list(project, grant, program, department)
ON  t.project = filter_list.project
    AND t.grant = filter_list.grant
    AND t.program = filter_list.program
    AND t.department = filter_list.department

提示：如果字段名是grant这类SQL Server保留关键字，需要用方括号[grant]包裹避免语法报错。

方案2：条件拼接法（适合匹配组合较少的场景） #

直接把每组匹配条件用AND组合后，再用OR拼接所有组合即可，无需额外关联逻辑：

WHERE
    (project = 'pj1' AND grant = 'gr1' AND program = 'pg1' AND department = 'dp1')
    OR (project = 'pj3' AND grant = 'gr2' AND program = 'pg1' AND department = 'dp2')
    OR (project = 'pj6' AND grant = 'gr3' AND program = 'pg2' AND department = 'dp1')

注意：每组组合条件必须用括号包裹，避免因为逻辑运算符优先级问题导致筛选结果错误。

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