在 FROM 子句中创建的表别名在 WHERE 子句中无法识别

Question

任务是"查找员工人数所在的大学Id从那所大学毕业的人是所有大学中最多的。

模式是这样的

Graduate ( EmpId: NUMERIC REFERENCES Employee(EmpId), 
UnivId: NUMERIC REFERENCES University(UnivId), 
GradYear: NUMERIC)
University ( UnivId: NUMERIC, UnivName: VARCHAR(40))
Employee (EmpId: NUMERIC, 
EmpName: VARCHAR(40))

我查询:

SELECT Temp.UnivId
FROM  (SELECT G.UnivId, COUNT(*) as Num
FROM Graduate G, Employee E
WHERE G.EmpId = E.EmpId
GROUP BY G.UnivId) AS Temp
WHERE Temp.Num = (SELECT MAX(Temp.Num) FROM Temp);

当我在psql控制台运行这个查询时，软件是PostgresSQL，它返回一个错误说关系"temp"不存在，它指向Temp在最后。有谁知道为什么吗?

Answer 1

您应该在这里使用RANK:

WITH cte AS (
SELECT g.UnivId, RANK() OVER (ORDER BY COUNT(e.EmpId) DESC) rnk
FROM Graduate g
INNER JOIN Employee e
ON g.EmpId = e.EmpId
GROUP BY g.UnivId
)
SELECT UnivId
FROM cte
WHERE rnk = 1;

注意，这种方法也可以很好地处理绑定，如果它们发生的话。

当前方法的问题是，您引用WHERE子句中的子查询，就好像它是一个独立的表一样，它不是。您可以将Temp子查询移动到CTE，然后您的方法就可以工作了:

WITH Temp AS (
SELECT G.UnivId, COUNT(*) as Num
FROM Graduate G, Employee E
WHERE G.EmpId = E.EmpId
GROUP BY G.UnivId
)
SELECT Temp.UnivId
FROM Temp
WHERE Temp.Num = (SELECT MAX(Temp.Num) FROM Temp);

Answer 2

您可以访问已别名Temp的查询列，但是您不能从中进行选择，因为您还没有创建视图。如果您想创建一个临时视图，请使用WITH子句。

顺便说一下，你不应该使用逗号分隔的连接。这是20世纪80年代使用的语法，直到显式连接([INNER] JOIN,LEFT [OUTER] JOIN

等)在1992年成为SQL标准。为什么要加入员工表呢?有一种方法可以解决这个问题:

select univid, count(*)
from graduate
group by univid
order by count(*) desc
fetch first row with ties;

下面是另一个:

select univid, cnt
from
(
select univid, count(*) as cnt, max(count(*)) over () as max_cnt
from graduate
group by univid
) t
where cnt = max_cnt;

这是你所尝试的:

with t as
(
select univid, count(*) as cnt
from graduate
group by univid
)
select *
from t
where cnt = (select max(cnt) from t);

Answer 3

应该更简单。将子查询移动到CTE中，然后按num降序排序，并选择最上面的结果。您不需要同时加入Graduate和Employee。
顺便说一句，temp是一个保留字，所以最好不要使用它作为标识符/名称。

with tmp as
(
select univid, count(*) as num
from graduate
group by univid
)
select univid
from tmp
order by num desc limit 1;

我认为CTE使SQL代码更具可读性，但你可以不使用CTE编写相同的代码。

select univid
from 
(
select univid, count(*) as num
from graduate
group by univid
) tmp
order by num desc limit 1;

然而，如果领带是一个问题，你最好使用@TimBiegeleisen的rank方法，仍然没有join。