SQL 如何从下表中检索最高和最低工资？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31717465/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to retrieve highest and lowest salary from following table?
提问by Thompson rollins
I have employee table
我有员工表
EMP_ID | F_NAME | L_NAME | SALARY | JOINING_DATE | DEPARTMENT
-----------------------------------------------------------------------------------
101 | John | Abraham | 100000 | 01-JAN-14 09.15.00.000000 AM | Banking
102 | Michel | Clarke | 800000 | | Insaurance
102 | Roy | Thomas | 70000 | 01-FEB-13 12.30.00.000000 PM | Banking
103 | Tom | Jose | 600000 | 03-FEB-14 01.30.00.000000 AM | Insaurance
105 | Jerry | Pinto | 650000 | 01-FEB-13 12.00.00.000000 PM | Services
106 | Philip | Mathew | 750000 | 01-JAN-13 02.00.00.000000 AM | Services
107 | TestName1 | 123 | 650000 | 01-JAN-13 12.05.00.000000 PM | Services
108 | TestName2 | Lname% | 600000 | 01-JAN-13 12.00.00.000000 PM | Insaurance
i want to find highest and lowest salary from above table in oracle sql. if i do
我想在oracle sql中从上面的表格中找到最高和最低工资。如果我做
select max(salary) from (select * from (select salary from employee) where rownum <2);
it returns MAX(SALARY)= 100000where it should return 800000
它返回MAX(SALARY)=100000它应该返回的地方800000
If I do
如果我做
select max(salary)
from (select * from (select salary from employee)
where rownum <3);
it returns MAX(SALARY)= 800000
它返回MAX(SALARY)=800000
If I do
如果我做
select min(salary)
from (select * from(select salary from employee)
where rownum < 2);
it will return MIN(SALARY)= 100000where it should return 70000.
它将返回MIN(SALARY)=100000它应该返回的位置70000。
What is wrong in this query?
这个查询有什么问题？
what should be the correct query?
正确的查询应该是什么？
回答by Mureinik
You don't need all these subqueries:
您不需要所有这些子查询：
SELECT MAX(salary), MIN(salary)
FROM employee
回答by MT0
Oracle 11g R2 Schema Setup:
Oracle 11g R2 架构设置：
CREATE TABLE employee ( EMP_ID, F_NAME, L_NAME, SALARY, JOINING_DATE, DEPARTMENT ) AS
SELECT 101, 'John', 'Abraham', 100000, TIMESTAMP '2014-01-01 09:15:00', 'Banking' FROM DUAL
UNION ALL SELECT 102, 'Michel', 'Clarke', 800000, NULL, 'Insurance' FROM DUAL
UNION ALL SELECT 102, 'Roy', 'Thomas', 70000, TIMESTAMP '2013-02-01 12:30:00', 'Banking' FROM DUAL
UNION ALL SELECT 103, 'Tom', 'Jose', 600000, TIMESTAMP '2014-02-03 01:30:00', 'Insurance' FROM DUAL
UNION ALL SELECT 105, 'Jerry', 'Pinto', 650000, TIMESTAMP '2013-02-01 12:00:00', 'Services' FROM DUAL
UNION ALL SELECT 106, 'Philip', 'Mathew', 750000, TIMESTAMP '2013-01-01 02:00:00', 'Services' FROM DUAL
UNION ALL SELECT 107, 'TestName1', '123', 650000, TIMESTAMP '2013-01-01 12:05:00', 'Services' FROM DUAL
UNION ALL SELECT 108, 'TestName2', 'Lname%', 600000, TIMESTAMP '2013-01-01 12:00:00', 'Insurance' FROM DUAL;
Query 1 - To find the highest-n salaries:
查询 1 - 要找到最高的工资：
SELECT *
FROM (
SELECT salary
FROM employee
ORDER BY salary DESC
)
WHERE rownum <= 3 -- replace with the number of salaries you want to retrieve.
结果：
| SALARY |
|--------|
| 800000 |
| 750000 |
| 650000 |
Query 2 - To find the lowest-n salaries:
查询 2 - 查找最低工资：
SELECT *
FROM (
SELECT salary
FROM employee
ORDER BY salary ASC
)
WHERE rownum <= 3 -- replace with the number of salaries you want to retrieve.
结果：
| SALARY |
|--------|
| 70000 |
| 100000 |
| 600000 |
回答by PT_STAR
so you want the 2nd highest and 2nd lowest salary? Check this out
所以你想要第二高和第二低的工资？看一下这个
select max(salary), min(salary) from employee
where salary < (select max(salary) from employee)
and salary > (select min(salary) from employee)
;
回答by Sumit Agrawal
1) For lowest salary.
select * from (
select empno,job,ename,sal
from emp order by sal)
where rownum=1;
2) For Highest salary.
select * from (
select empno,job,ename,sal
from emp order by sal desc)
where rownum=1;
回答by Jhay-ar Perez
i don't know why you make complicated queries you can simply write this and get the same result:
我不知道你为什么要进行复杂的查询，你可以简单地写下这个并得到相同的结果：
select salary
from employees
where rownum <=3
order by salary desc;
回答by Harsh Bafna
For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.
对于查询返回的每一行，ROWNUM 伪列返回一个数字，指示 Oracle 从表或连接行集中选择行的顺序。选定的第一行的 ROWNUM 为 1，第二行的 ROWNUM 为 2，依此类推。
So in your case :
所以在你的情况下：
select max(salary) from (select * from (select salary from employee) where rownum <2);
This query will return
此查询将返回
101 John Abraham 100000 01-JAN-14 09.15.00.000000 AM Banking
only this row as output... and hence the max value will be 100000 only.
只有这一行作为输出......因此最大值仅为 100000。
select max(salary) from (select * from (select salary from employee) where rownum <3);
This query will tak first 2 rows from your table, i.e.,
此查询将从您的表中获取前 2 行，即，
101 John Abraham 100000 01-JAN-14 09.15.00.000000 AM Banking
102 Michel Clarke 800000 Insaurance
and hence the max salary will be 800000.
因此最高工资将是 800000。
Similarly,
相似地，
select min(salary)from (select * from(select salary from employee)where rownum<2);
will only select 1st row
只会选择第一行
select min(salary)from (select * from(select salary from employee)where rownum<2);
so min salary will be 100000.
所以最低工资将是 100000。
P.S. : You could simply write your queries like this :
PS：您可以简单地编写这样的查询：
select max(salary) from employee where rownum<[n];
where n will be ROWNUM to which you want to limit the number of rows returned by your query
其中 n 将是您想要限制查询返回的行数的 ROWNUM
回答by saphsys
Try it:
尝试一下：
select *
from (
select T.*, rownum RRN
from (
select salary
from employee
order by salary desc) T)
where RRN < 3
