在SQL中将WITH语句重写为子查询语句？

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!