SQL Server中双CTE写法报错的原因及解决方案

1. 语法规则违反：IN运算符的合法用法是后跟括号包裹的子查询或逗号分隔的值列表，直接写CTE名称temp2不符合SQL Server语法规范，因此触发"temp2附近语法错误"。
2. 括号使用后的误解：写成IN (temp2)时，SQL Server会将括号内的temp2解析为列名而非CTE对象，但temp1中不存在名为temp2的列，所以提示"temp2是无效列名"。
3. 逻辑偏差：原单CTE语句使用NOT IN排除特定记录，改写时误写为IN，即便语法修复，结果也会与原逻辑完全相反。

方案1：修正IN子查询语法+还原原逻辑 #

将temp2作为子查询放入IN的括号内，同时改回NOT IN以匹配原查询逻辑：

WITH temp1 AS(
    SELECT h.hacker_id as id, MIN(h.name) AS name, COUNT(h.hacker_id) AS n_challenge 
    FROM Hackers h JOIN Challenges c ON h.hacker_id = c.hacker_id
    GROUP BY h.hacker_id
),
temp2 AS(
    SELECT n_challenge FROM temp1
    WHERE n_challenge != (SELECT MAX(n_challenge) FROM temp1)
    GROUP BY n_challenge
    HAVING COUNT(n_challenge) > 1 
)
SELECT * FROM temp1
WHERE n_challenge NOT IN (SELECT n_challenge FROM temp2)
ORDER BY n_challenge DESC, id;

方案2：用LEFT JOIN替代NOT IN（性能更优） #

通过LEFT JOIN关联两个CTE，筛选出temp2中无匹配的记录，既实现逻辑复用，又避免NOT IN可能带来的隐性问题：

WITH temp1 AS(
    SELECT h.hacker_id as id, MIN(h.name) AS name, COUNT(h.hacker_id) AS n_challenge 
    FROM Hackers h JOIN Challenges c ON h.hacker_id = c.hacker_id
    GROUP BY h.hacker_id
),
temp2 AS(
    SELECT n_challenge FROM temp1
    WHERE n_challenge != (SELECT MAX(n_challenge) FROM temp1)
    GROUP BY n_challenge
    HAVING COUNT(n_challenge) > 1 
)
SELECT t1.* 
FROM temp1 t1
LEFT JOIN temp2 t2 ON t1.n_challenge = t2.n_challenge
WHERE t2.n_challenge IS NULL
ORDER BY t1.n_challenge DESC, t1.id;

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