为什么SQL Server的IN子句使用两列时会报语法错误？

核心原因 #

SQL Server 不支持 IN 子句同时匹配多个列的语法，你代码中where (P1,P2) in (select P1,P2 from ...)的写法属于MySQL、PostgreSQL等其他数据库的兼容语法，在SQL Server中会触发对应的语法错误。

可行实现方案 #

方案1：改用 EXISTS 关联匹配 #

和你原有逻辑的兼容性最高，修改成本最低：

with cte as (
    select pe1.name as P1,
        pe2.name as P2,
        m.title as T
    from participant pa1
    join participant pa2 on pa2.idMeeting = pa1.idMeeting
        and pa2.idPerson > pa1.idPerson
    join person pe1 on pe1.id = pa1.idPerson
    join person pe2 on pe2.id = pa2.idPerson
    join meeting m on m.id = pa1.idMeeting
),
cte_meet_max as (
    select count(*) cnt, P1,P2
    from cte
    group by P1,P2    
)
select *
from cte a
where exists (
    select 1
    from cte_meet_max b
    where b.cnt = (select max(cnt) from cte_meet_max)
        and a.P1 = b.P1 
        and a.P2 = b.P2
);

方案2：用窗口函数简化逻辑 #

可以省去一层子查询，执行效率也更优：

with cte as (
    select pe1.name as P1,
        pe2.name as P2,
        m.title as T
    from participant pa1
    join participant pa2 on pa2.idMeeting = pa1.idMeeting
        and pa2.idPerson > pa1.idPerson
    join person pe1 on pe1.id = pa1.idPerson
    join person pe2 on pe2.id = pa2.idPerson
    join meeting m on m.id = pa1.idMeeting
),
cte_with_cnt as (
    select *,
        count(*) over(partition by P1,P2) as meet_cnt,
        dense_rank() over(order by count(*) over(partition by P1,P2) desc) as rk
    from cte
)
select P1,P2,T 
from cte_with_cnt 
where rk = 1;

最终效果 #

两种写法都可以实现需求：查询出共同参会次数最多的两人组合对应的所有参会记录，结果示例如下：

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