好的,所以我被困在某事上,我需要你的幫助。這怎麼能正常運行:
select salary from employees
where job_id = 'AD_PRES' and rownum <= 5
order by salary desc
這不是嗎?
select * from employees
where
salary in
(
select salary from employees
where job_id = 'AD_PRES' and rownum <= 5
order by salary desc
)
我得到的錯誤是: 00907. 00000 - "missing right parenthesis"
在 SQL 中,一般情況下,表的排序是不確定的,不能保證一個順序。
您的查詢:
select salary
from employees
where job_id = 'AD_PRES'
and rownum <= 5
order by salary desc;
將以非確定性(隨機)順序從表中獲取行1然後它將應用該WHERE
子句僅包含行,job_id = 'AD_PRES'
並ROWNUM
按照讀取的順序將偽列值分配給行,並在找到後停止5 行。之後,它將按照薪水的順序對前(隨機)5 行進行排序。
您想要的是ORDER BY
在過濾之前應用該子句ROWNUM
:
SELECT salary
FROM (
SELECT salary
FROM employees
WHERE job_id = 'AD_PRES'
ORDER BY salary DESC
)
WHERE rownum <= 5
從 Oracle 12 開始,有一個更簡單的語法:
SELECT salary
FROM employees
WHERE job_id = 'AD_PRES'
ORDER BY salary desc
FETCH FIRST 5 ROWS ONLY;
這怎麼能正常運行……而這不行?
正如@OldProgrammer 所說,因為IN
是一個集合操作,將順序應用於無序集合是沒有意義的,並且語法不允許ORDER BY
在這種情況下使用子句。該"missing right parenthesis"
錯誤是由於提高甲骨文的不期望ORDER BY
子句中的IN
列表,取而代之的是,確實希望子查詢與一個右括號關閉
您需要做的是使用不在ORDER BY
子查詢最外層查詢中的查詢或使用新FETCH
語法。
select *
from employees
where salary in (
SELECT salary
FROM (
SELECT salary
FROM employees
WHERE job_id = 'AD_PRES'
ORDER BY salary DESC
-- You can use `ORDER BY` in a nested sub-query
)
WHERE rownum <= 5
-- No ORDER BY clause in the outer-most sub-query of an IN expression
)
或者:
select *
from employees
where salary in (
SELECT salary
FROM employees
WHERE job_id = 'AD_PRES'
ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY
)
db<>在這裡擺弄
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句