通常在SQL查询中使用WHERE
子句,在WHERE中,我们从表中检查是否满足条件,但使用以下代码:
SELECT name
FROM teacher as T1
WHERE (
SELECT COUNT (DISTINCT T2.salary)
FROM teacher as T2
WHERE T2.salary > T1.salary
) = 3;
下面的子查询Sub
正在返回一个数字。
(SELECT COUNT (DISTINCT T2.salary)
FROM teacher as T2
WHERE T2.salary > T1.salary)
但我不明白我们是如何检查WHERE Sub = 3
的,这对我来说没有意义。你能解释一下吗?
整个查询发现教师工资排名第四。
您可以在任何可以使用表达式的地方使用子查询,为什么不在等式运算符的左侧呢?
但这种解决问题的方法相当丑陋。为什么不
SELECT name
FROM (SELECT DISTINCT ON (salary)
name, salary
FROM teacher) AS t
ORDER BY salary DESC
OFFSET 3 FETCH FIRST 1 ROWS ONLY;
也许这个查询更全面:
WITH T AS
(
SELECT name, COUNT(*) OVER(ORDER BY salary) AS N
FROM teacher as T1
)
SELECT name
FROM T
WHERE N > 3