如果我像這樣運行代碼，我會得到我需要的結果，但我還需要添加名稱列，一旦添加，結果就會改變
select department_id, max(salary)
from employees e1
where salary <
(select max(salary)
from employees e2
where e2.department_id=e1.department_id)
group by department_id
order by department_id;
uj5u.com熱心網友回復：
我沒有你的桌子，所以我會用 Scott 的EMP。這是它的內容：
SQL> select deptno, ename, sal from emp order by deptno, sal desc;
DEPTNO ENAME SAL
---------- ---------- ----------
10 KING 5000
10 CLARK 2450 --> 2nd highest in deptno 10
10 MILLER 1300
20 SCOTT 3000
20 FORD 3000
20 JONES 2975 --> 2nd highest in deptno 20
20 ADAMS 1100
20 SMITH 800
30 BLAKE 2850
30 ALLEN 1600 --> 2nd highest in deptno 30
30 TURNER 1500
30 MARTIN 1250
30 WARD 1250
30 JAMES 950
14 rows selected.
這是你不想要的：
SQL> with temp as
2 (select deptno, ename, sal,
3 dense_rank() over (partition by deptno order by sal desc) rnk
4 from emp
5 )
6 select *
7 from temp
8 where rnk = 2
9 order by deptno, sal desc;
DEPTNO ENAME SAL RNK
---------- ---------- ---------- ----------
10 CLARK 2450 2
20 JONES 2975 2
30 ALLEN 1600 2
SQL>
好的，那么讓我們關聯一些子查詢。返崗員工工資為
- 低于他們部門的最高水平（第 6 行）（它將排名第一）
- 他們部門其他薪水最高的（第 3 行）
所以：
SQL> select e.deptno, e.ename, e.sal
2 from emp e
3 where e.sal = (select max(b.sal)
4 from emp b
5 where b.deptno = e.deptno
6 and b.sal < (select max(a.sal)
7 from emp a
8 where a.deptno = b.deptno
9 group by a.deptno
10 )
11 )
12 order by e.deptno;
DEPTNO ENAME SAL
---------- ---------- ----------
10 CLARK 2450
20 JONES 2975
30 ALLEN 1600
SQL>
uj5u.com熱心網友回復：
您可以將 row_number() 視窗函式與公用表運算式一起使用，而不是使用子查詢：
with cte as
(
select department_id, row_number()over(partition by department_id order by salary
desc) rn, name
from employees e1
)
select department_id, salary, name
from cte where rn=2
只需在選擇串列中添加名稱列就可以了
select department_id, max(salary),name
from employees e1
where salary <
(select max(salary)
from employees e2
where e2.department_id=e1.department_id)
group by department_id
order by department_id;
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/426885.html
上一篇：oracle欄位組合
下一篇：oracle中的多個字串搜索