我有以下两种关系：
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,Game.name) GamesDevs WHERE GamesDevs.DevsCount = (SELECT MAX(DevsCount) FROM GamesDevs)
据我所知,这两个查询应该是相同的.但是,当我尝试运行第二个查询时,我收到错误
Msg 207,Level 16,State 1,Line 6 Invalid column name ‘DevsCount’.
解决方法
您将需要在最后一个子句中复制该子查询,如：
SELECT *
FROM (SELECT Game.name AS GameName,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
