为什么双CTE在这种情况下不起作用

Question

以下是在给定初始表Hackers:的SQL Server中编译的内容

WITH temp 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
)
SELECT * FROM temp
WHERE n_challenge NOT IN (
SELECT n_challenge FROM temp
WHERE n_challenge != (SELECT MAX(n_challenge) FROM temp)
GROUP BY n_challenge
HAVING COUNT(n_challenge) > 1 
)
ORDER BY n_challenge DESC, id;

然而，我想做一个小的改变，把选择块：

SELECT n_challenge FROM temp
WHERE n_challenge != (SELECT MAX(n_challenge) FROM temp)
GROUP BY n_challenge
HAVING COUNT(n_challenge) > 1 

在SELECT * FROM temp行上方使用另一个WITH子句，以便我可以多次引用该块。然而，当我尝试这个：

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 IN temp2
ORDER BY n_challenge DESC, id;

它不会编译，在temp2附近说无效语法。我试着把temp2括在括号里，但错误是temp2是无效的列名。任何人都可以解释为什么会失败，以及将select查询存储在临时名称中的解决方案？

Answer 1

在您的情况下，不能将IN与表名或CTE一起使用。

相反，您应该使用子查询，如：

IN (SELECT n_challenge FROM temp2)

或者可以使用类似的联接

SELECT t1.* 
FROM temp1 t1
JOIN temp2 t2 ON t1.n_challenge = t2.n_challenge 
ORDER BY t1.n_challenge DESC, id;