SQL 从员工表中选择最早雇用的员工
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8262690/
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
Select earliest hired employee from employee table
提问by dato datuashvili
I have an employee table where the fields are:
我有一个员工表，其中的字段是：
first_name, last_name, hire_date, salary, department_id, department_name, and so on.
first_name、last_name、hire_date、salary、department_id、department_name 等等。
I am asked to find most earliest hired employees. I know functions like max, sysdate, and months_between, but I don't know how to use it here?
我被要求找到最早雇佣的员工。我知道像 max、sysdate 和months_between 这样的函数，但我不知道如何在这里使用它？
I was thinking that this problem could be solved like this:
我在想这个问题可以这样解决：
select e.first_name,max(e.hire_date)
from employees e
but it generates the following error:
但它会产生以下错误：
oRA-00937: not a single-group group function
00937. 00000 - "not a single-group group function"
*Cause:
*Action:
Error at Line: 1 Column: 7
So please give me hint what will be correct form?
所以请给我提示什么是正确的形式？
回答by p.campbell
To find the earliest hired employee:
要找到最早雇用的员工：
SELECT * FROM
(
SELECT *
FROM employees
ORDER BY hire_date
)
WHERE rownum = 1;
If you want the top 10 earliest hired employees:
如果你想要前 10 名最早雇佣的员工：
SELECT * FROM
(
SELECT *
FROM employees
ORDER BY hire_date
)
WHERE rownum <= 10;
This is using Oracle's pseudocolumn ROWNUM
这是使用 Oracle 的伪列 ROWNUM
回答by Twelfth
It'll be min for earliest, not max.
最早会是最小值，而不是最大值。
Alternate route to order by and rownum solution
order by 和 rownum 解决方案的替代路线
select min(hire_date) as earliest_hire_date from employees
That gets the earliest hire date. Call it a subquery and join back to employees to get rest of information you need
那得到最早的雇用日期。将其称为子查询并返回给员工以获取您需要的其余信息
select e.*, earliest_hire_date
from (select min(hire_date) as earliest_hire_date from employees) a
inner join employees e on e.hire_date = a.earliest_hire_date
Inner join functions as a filter here. Advantage to this method is if you have multiple employees hired on the same day, it will bring back multiple rows...one for each employee hired on that date.
内部连接在这里用作过滤器。这种方法的优点是，如果您在同一天雇佣了多名员工，它会带回多行……对于当天雇佣的每个员工，都会带回多行。
If you're more comfortable without the inner join, this is equivalent:
如果您在没有内部连接的情况下更舒服，则相当于：
select e.*, earliest_hire_date
from (select min(hire_date) as earliest_hire_date from employees) a, employees
where e.hire_date = a.earliest_hire_date
回答by Jake Feasel
select * from employee
where
not exists
(select 1 from employee e2 where e2.hire_date < employee.hire_date)
回答by DCookie
An analytic could work as well:
分析也可以工作：
SELECT emp_name, hire_date
FROM (SELECT first_name, hire_date
, dense_rank() OVER (ORDER BY hire_date) rnk
FROM employees e)
WHERE rnk = 1;
回答by P.Sharan
This will also work: If you want to display the data along with employee name and hiredate without using joins
这也将起作用：如果您想在不使用连接的情况下显示数据以及员工姓名和雇员
select emp_name as ELDER_EMPLOYEE,hire_date
from employees
where hire_date=(select min(hire_date) from employees)
