我有以下两个关系:

Game(id, name, year) 
Devs(pid, gid, role) 

其中 Game.id 是主键，而 Devs.gid 是 Game.id 的外键。

在 previous post I made here ，另一位用户非常友好地帮助我创建了一个查询，该查询查找由制作该游戏的大多数开发人员制作的所有游戏。他的回答使用了 WITH 语句，我对这些不是很熟悉，因为我只有几周的时间学习 SQL。这是工作查询:

WITH GamesDevs (GameName, DevsCount) 
AS 
( 
    SELECT Game.name AS GameName, count(DISTINCT Devs.pid) AS DevsCount 
    FROM Game, Devs 
    WHERE Devs.gid=Game.id 
    GROUP BY Devs.gid, Game.name 
) 
 
SELECT * FROM GamesDevs WHERE GamesDevs.DevsCount = (SELECT MAX(DevsCount) FROM GamesDevs) 

为了更加熟悉 SQL，我尝试使用子查询而不是 WITH 语句重写此查询。我一直在用 this Oracle documentation帮我弄清楚。我尝试像这样重写查询:

SELECT * 
FROM (SELECT Game.name AS GameName, count(DISTINCT Devs.pid) AS DevsCount 
    FROM Game, Devs 
    WHERE Devs.gid=Game.id 
    GROUP BY Devs.gid, Game.name) GamesDevs 
WHERE GamesDevs.DevsCount = (SELECT MAX(DevsCount) FROM GamesDevs) 

据我所知，这两个查询应该是相同的。但是，当我尝试运行第二个查询时，出现错误

Msg 207, Level 16, State 1, Line 6 Invalid column name 'DevsCount'.

有谁知道为什么我可能会收到此错误，或者为什么这两个查询不相同？

请您参考如下方法:

您需要在最后一个 from 子句中复制该子查询，例如:

SELECT * 
FROM (SELECT Game.name AS GameName, count(DISTINCT Devs.pid) AS DevsCount 
    FROM Game, Devs 
    WHERE Devs.gid=Game.id 
    GROUP BY Devs.gid, Game.name) GamesDevs 
WHERE GamesDevs.DevsCount = (SELECT MAX(DevsCount) FROM (SELECT Game.name AS GameName, count(DISTINCT Devs.pid) AS DevsCount 
    FROM Game 
    INNER JOIN Devs ON Devs.gid=Game.id 
    GROUP BY Devs.gid, Game.name)) 

但最好这样做:

SELECT TOP 1 WITH TIES Game.name AS GameName, count(DISTINCT Devs.pid) AS DevsCount 
FROM Game 
INNER JOIN Devs ON Devs.gid=Game.id 
GROUP BY Devs.gid, Game.name 
ORDER BY DevsCount DESC