我如何使用"over(partition by)"获得第二个最高薪水?在Oracle SQL? [英] How I can get Second max salary using "over(partition by)" in oracle SQL?
本文介绍了我如何使用"over(partition by)"获得第二个最高薪水?在Oracle SQL?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我已经通过执行此查询来获取它:
I already get it by doing this query:
SELECT *
FROM
(
SELECT emp_id,salary,row_number() over(order by salary desc) AS rk
FROM test_qaium
)
where rk=2;
但是我的一个朋友要我从雇员表中查找第二个MAX
工资,必须在oracle sql中使用"over(partition by )
".有人请帮助我.
并让我明白oracle sql中"Partition by
"的概念.
But one of my friend ask me to find second MAX
salary from employees table must using "over(partition by )
" in oracle sql. Anybody please help me.
And clear me the concept of "Partition by
" in oracle sql.
推荐答案
Oracle安装程序:
CREATE TABLE test_qaium ( emp_id, salary, department_id ) AS
SELECT 1, 10000, 1 FROM DUAL UNION ALL
SELECT 2, 20000, 1 FROM DUAL UNION ALL
SELECT 3, 30000, 1 FROM DUAL UNION ALL
SELECT 4, 40000, 1 FROM DUAL UNION ALL -- One highest, one 2nd highest
SELECT 5, 10000, 2 FROM DUAL UNION ALL
SELECT 6, 20000, 2 FROM DUAL UNION ALL
SELECT 7, 30000, 2 FROM DUAL UNION ALL
SELECT 8, 30000, 2 FROM DUAL UNION ALL -- Two highest, one 2nd highest
SELECT 9, 10000, 3 FROM DUAL UNION ALL
SELECT 10, 10000, 3 FROM DUAL UNION ALL -- Two highest, no 2nd highest
SELECT 11, 10000, 4 FROM DUAL UNION ALL -- One highest, no 2nd highest
SELECT 12, 20000, 5 FROM DUAL UNION ALL
SELECT 13, 20000, 5 FROM DUAL UNION ALL
SELECT 14, 30000, 5 FROM DUAL; -- One highest, Two 2nd highest
查询:
这将获得每个部门第二高薪的所有行:
This will get all the rows with the 2nd highest salary for each department:
SELECT *
FROM (
SELECT t.*,
DENSE_RANK() OVER (PARTITION BY department_id
ORDER BY salary DESC) AS rnk
FROM test_qaium t
)
WHERE rnk=2;
输出:
EMP_ID SALARY DEPARTMENT_ID RNK
------ ------ ------------- ---
3 30000 1 2
6 20000 2 2
12 20000 5 2
13 20000 5 2
这篇关于我如何使用"over(partition by)"获得第二个最高薪水?在Oracle SQL?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文