从from中生成的子查询中获取max属性的行

Question

SELECT pname, devcost
FROM (
SELECT P.pname, budget / COUNT(*) as devcost
FROM project P,
devassignments D
WHERE P.pname = D.pname
GROUP BY P.pname) as t
WHERE devcost = (SELECT MAX(devcost)
FROM t)
ORDER BY pname
;

在上面的SQL查询中，我想获得与表t的最大devcost相对应的行。然而，当我运行此查询时，我得到一个错误

ERROR: relation "t" does not exist

对此最好的方法是什么？顺便说一句，如果我以后不能在查询中引用别名t，那么定义它有什么意义？

谢谢！

Answer 1

使用窗口函数。以及正确、明确、标准、可读的JOIN语法：

SELECT pname, devcost
FROM (SELECT P.pname, budget / COUNT(*) as devcost,
RANK() OVER (ORDER BY budget / COUNT(*) DESC) as seqnum
FROM project P JOIN
devassignments D
ON P.pname = D.pname
GROUP BY P.pname
) n
WHERE seqnum = 1
ORDER BY pname