MySQL - 多列中具有相同值的行

Question

我需要一个将显示以下行的查询:

first_name   last_name     dept_no  hire_date   from_date
----------------------------------------------------------
Jianwen       Schiper       d004    1986-10-17  1986-10-17
Sanjiv        Zschoche      d004    1986-10-17  1986-10-17
Moty          Winter        d004    1991-04-28  1991-04-28
Zhilian       Ranst         d004    1991-04-28  1991-04-28
Jaewoo        Yoshimura     d004    1991-04-28  1991-04-28
Herbert       Zizka         d005    1985-03-21  1985-03-21
Arno          Luke          d005    1985-03-21  1985-03-21
Muzhong       Shokrollahi   d007    1985-09-05  1985-09-05
Isaac         Bael          d007    1985-09-05  1985-09-05
Mani          Cannata       d007    1989-06-21  1989-06-21
Lidong        Tomescu       d007    1989-06-21  1989-06-21
Taizo         Kitsuregawa   d007    1991-09-03  1991-09-03
Bodo          Tagansky      d007    1991-09-03  1991-09-03

简而言之，我需要同一部门同一日期雇用的员工。以下查询为我提供了员工的第一个部门和雇用日期:

select first_name, last_name, dept_no, hire_date, from_date
from employees join dept_emp using(emp_no)
where hire_date = from_date 
order by hire_date, dept_no

我需要将同一部门在同一天受聘的人员分开。我尝试了太多查询，但无法得到我需要的东西。预先感谢您的帮助，并对英语不好表示抱歉。

Answer 1

如果您只想看到一起雇用的员工，那么您可以订购结果:

select first_name, last_name, dept_no, hire_date, from_date
from employees join
     dept_emp
     using (emp_no)
order by dept_no, hire_date;

如果您想要雇用至少两名员工的日期(以及该日期的员工):

select first_name, last_name, dept_no, hire_date, from_date
from employees e join
     dept_emp d
     using (emp_no)
where exists (select 1
              from employees e2
              where e2.dept_no = e.dept_no and
                    e2.hire_date = e.hire_date and
                    e2.emp_no <> e.emp_no
             )
order by dept_no, hire_date;