sql - 为什么我在 SQL Server 'in' 子句中出现语法错误？

Question

我在 SQL Server 中编写了一个查询，但出现错误。

代码：

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 
where (P1,P2) in (
    select P1,P2 
    from cte_meet_max 
    where cnt = (select max(cnt) from cte_meet_max)
);

错误：

消息 4145，级别 15，状态 1，第 25 行
在预期条件的上下文中指定的非布尔类型表达式，靠近“，”。

期望的结果：

我们可以在 SQL Server 中放置带有“in”子句的两列吗？

请帮忙。

提前致谢。

注意：我尝试解决问题之前发布的问题

标签： sqlsql-servertsql

Answer 1

EXISTS 条件是在 SQL Server 中重写它的正确方法：

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 
where exists (
    select *
    from cte_meet_max m1
    where m1.cnt = (select max(m2.cnt) from cte_meet_max m2)
      and m1.p1 = cte.p1
      and m1.p2 = cte.p2
)