[编程题]获取所有部门中当前员工薪水最高的相关信息

题目描述

获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

输入描述:

无

输出描述:

dept_no	emp_no	salary
d001	10001	88958
d002	10006	43311
d003	10005	94692
d004	10004	74057
d005	10007	88070
d006	10009	95409

示例1

输入

复制

无

输出

复制

无

--[编程题]获取所有部门中当前员工薪水最高的相关信息 
SELECT de.dept_no, de.emp_no, s.salary
FROM dept_emp de
	INNER JOIN salaries s
	ON (de.emp_no = s.emp_no
		AND de.to_date = '9999-01-01'
		AND s.to_date = '9999-01-01')
WHERE s.salary = (
	SELECT MAX(s2.salary)
	FROM dept_emp de2
		INNER JOIN salaries s2
		ON (de2.emp_no = s2.emp_no
			AND de2.to_date = '9999-01-01'
			AND s2.to_date = '9999-01-01')
	WHERE de2.dept_no = de.dept_no
	GROUP BY de2.dept_no
)
ORDER BY de.dept_no;
--第二种方法
SELECT re1.dept_no, re2.emp_no, re1.salary
FROM (
	SELECT dept_em.dept_no, MAX(em_sa.salary) AS salary
	FROM (
		SELECT emp_no, salary
		FROM salaries
		WHERE to_date = '9999-01-01'
	) em_sa
		LEFT JOIN (
			SELECT emp_no, dept_no
			FROM dept_emp
			WHERE to_date = '9999-01-01'
		) dept_em
		ON em_sa.emp_no = dept_em.emp_no
	GROUP BY dept_no
) re1
	JOIN (
		SELECT dept_em.dept_no, em_sa.emp_no, em_sa.salary AS salary
		FROM (
			SELECT emp_no, salary
			FROM salaries
			WHERE to_date = '9999-01-01'
		) em_sa
			LEFT JOIN (
				SELECT emp_no, dept_no
				FROM dept_emp
				WHERE to_date = '9999-01-01'
			) dept_em
			ON em_sa.emp_no = dept_em.emp_no
	) re2
	ON re1.dept_no = re2.dept_no
		AND re1.salary = re2.salary
ORDER BY re1.dept_no;
--第三种方法
SELECT d.dept_no, d.emp_no, s.salary
FROM dept_emp d
	JOIN salaries s ON d.emp_no = s.emp_no
WHERE d.to_date = '9999-01-01'
	AND s.to_date = '9999-01-01'
GROUP BY d.dept_no
HAVING MAX(s.salary);
--第四种方法
SELECT
	d.dept_no,
	d.emp_no,
	MAX( s.salary ) AS salary 
FROM
	dept_emp d,
	salaries s 
WHERE
	( d.emp_no = s.emp_no AND d.to_date = '9999-01-01' AND s.to_date = '9999-01-01' ) 
GROUP BY
	d.dept_no;
--第五种方法
/* 此题思路如下：
1、先用INNER JOIN连接两张表，限制条件是两张表的emp_no相同，即d.emp_no = s.emp_no；
2、选取每个员工当前的工资水平，用d.to_date = '9999-01-01' AND s.to_date = '9999-01-01'作条件限制，因为此表中每条最新记录的 to_date 都用 9999-01-01 表示；
3、用GROUP BY d.dept_no将每个部门分为一组，用MAX()函数选取每组中工资最高者；
4、将salaries用s代替，dept_emp用d代替，最后将MAX(s.salary)用salary代替后输出。 */
SELECT d.dept_no, s.emp_no, MAX(s.salary) AS salary
FROM salaries AS s INNER JOIN dept_emp As d
ON d.emp_no = s.emp_no 
WHERE d.to_date = '9999-01-01' AND s.to_date = '9999-01-01'
GROUP BY d.dept_no