这个Oracle SQL代码的含义是什么？

Question

我最近一直在学习Oracle SQL，我遇到了以下代码:

SELECT * 
FROM employees outer_emps
WHERE (2-1) = (
SELECT COUNT(*)
FROM employees inner_emps
WHERE inner_emps.salary > outer_emps.salary
);

有人能帮我理解一下语法吗?

Answer 1

返回工资第二高的员工。

例如:

SQL> select ename, sal from emp order by sal;
ENAME             SAL
---------- ----------
SMITH             840
JAMES             950
ADAMS            1100
WARD             1250
MARTIN           1250
MILLER           1300
TURNER           1500
ALLEN            1600
CLARK            2450
BLAKE            2850
JONES            2975
SCOTT            3000   --> SCOTT and FORD have the 2nd highest salary
FORD             3000   -->
KING             5000   --> KING has the highest salary
14 rows selected.
SQL> select ename, sal
2  from emp outer_emps
3  where 1 = (select count(*) from emp inner_emps
4             where inner_emps.sal > outer_emps.sal);
ENAME             SAL
---------- ----------
SCOTT            3000
FORD             3000
SQL>

如果你使用<，你会得到第二低的工资(这是詹姆斯，因为史密斯的工资是840 -最低-詹姆斯紧随其后950):

SQL> select ename, sal
2  from emp outer_emps
3  where 1 = (select count(*) from emp inner_emps
4             where inner_emps.sal < outer_emps.sal);
ENAME             SAL
---------- ----------
JAMES             950
SQL>

---

我可能会这样做;这样更容易理解:

SQL> with temp as
2    (select ename, sal,
3       rank() over (order by sal desc) rnk
4     from emp
5    )
6  select ename,sal from temp
7  where rnk = 2;
ENAME             SAL
---------- ----------
SCOTT            3000
FORD             3000
SQL>