[oracle]发现最早的雇员在每个部门 [重复]

标签: sql Oracle
发布时间: 2017/5/5 20:34:24
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

此问题已经在这里有了答案︰

我有下面的代码在每个部门中查找最早期的雇佣的员工

select  d.department_name,e.first_name || ' '||  e.last_name,e.hire_date,min(e.hire_date)
      from employees e join departments d
      on e.department_id=d.department_id
   group by d.department_name,e.hire_date

但它展示了以下错误

ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"
*Cause:    
*Action:
Error at Line: 13 Column: 26

请帮我这段代码有什么错误吗?

解决方法 1:

一旦你得到的最早的雇用日期为每个部门,你可以到雇员表,看看谁你雇了那日期从该部门内部联接。

编辑︰ 这样的事情应该工作。

select 
    a.department_name,
    a.EarliestHireDate,
    b.first_name + ' ' + b.last_name AS EmployeeName
from
    (
      select min(e.hire_date) as  EarliestHireDate, d.department_name, d.department_id
      from employees e join departments d
        on e.department_id=d.department_id
      group by d.department_name
    ) as a
    inner join employees as b
        on a.EarliestHireDate = b.hire_date
            and a.department_id = b.department_id

如果不止一个被雇那部那最早的租用一天,你会得到每个部门的职工超过 1

赞助商