假设有一个员工表，包含员工编号（emp_id）、员工姓名（emp_name）、员工所属部门编号（dept_id）和员工入职时间（join_date）四个字段。请编写 SQL 语句，查询出每个部门的最早入职员工信息。列出所有不重复的方法

好的，以下是列出的 10 种方法：

方法一：

SELECT emp_id, emp_name, dept_id, join_date
FROM employee e1
WHERE join_date = (
    SELECT MIN(join_date)
    FROM employee e2
    WHERE e1.dept_id = e2.dept_id
);

方法二：

SELECT emp_id, emp_name, dept_id, join_date
FROM employee
WHERE (dept_id, join_date) IN (
    SELECT dept_id, MIN(join_date)
    FROM employee
    GROUP BY dept_id
);

方法三：

SELECT emp_id, emp_name, dept_id, join_date
FROM (
    SELECT emp_id, emp_name, dept_id, join_date,
           ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY join_date) AS rn
    FROM employee
) e
WHERE rn = 1;

方法四：

SELECT emp_id, emp_name, dept_id, join_date
FROM employee e1
WHERE NOT EXISTS (
    SELECT 1
    FROM employee e2
    WHERE e2.dept_id = e1.dept_id AND e2.join_date < e1.join_date
);

方法五：

SELECT emp_id, emp_name, dept_id, join_date
FROM employee e1
GROUP BY e1.dept_id
HAVING join_date = MIN(join_date);

方法六：

SELECT e1.emp_id, e1.emp_name, e1.dept_id, e1.join_date
FROM employee e1
JOIN (
    SELECT dept_id, MIN(join_date) AS join_date
    FROM employee
    GROUP BY dept_id
) e2 ON e1.dept_id = e2.dept_id AND e1.join_date = e2.join_date;

方法七：

SELECT emp_id, emp_name, dept_id, join_date
FROM employee e1
WHERE NOT EXISTS (
    SELECT 1
    FROM employee e2
    WHERE e2.dept_id = e1.dept_id
    GROUP BY e2.dept_id
    HAVING MIN(e2.join_date) < e1.join_date
);

方法八：

SELECT e1.emp_id, e1.emp_name, e1.dept_id, e1.join_date
FROM employee e1
LEFT JOIN employee e2 ON e1.dept_id = e2.dept_id AND e1.join_date > e2.join_date
WHERE e2.emp_id IS NULL;

方法九：

SELECT e1.emp_id, e1.emp_name, e1.dept_id, e1.join_date
FROM employee e1
INNER JOIN (
    SELECT dept_id, MIN(join_date) AS join_date
    FROM employee
    GROUP BY dept_id
) e2 ON e1.dept_id = e2.dept_id AND e1.join_date = e2.join_date;

方法十：

SELECT emp_id, emp_name, dept_id, join_date
FROM (
    SELECT emp_id, emp_name, dept_id, join_date, RANK() OVER (PARTITION BY dept_id ORDER BY join_date) AS rank
    FROM employee
) e
WHERE rank = 1;