describe

Alter table dept_emp;

There is a salary table as follows:

SQL > select dept_NO, emp_NO, salary from dept_no, dept_NO, dept_NO, dept_NO, dept_NO, dept_NO, dept_NO, dept_NO, dept_NO, dept_NO, dept_NO, dept_NO, dept_NO

Example 1

Input:

drop table if exists `dept_emp` ; drop table if exists `salaries` ; 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`)); INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01'); INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01'); INSERT INTO salaries VALUES (58, 10001889 '2002-06-22', '9999-01-01'); INSERT INTO salaries VALUES (27, 10002725, '2001-08-02', '9999-01-01');Copy the code

Output: d001 | 10001 | 88958

Error model

It passes the test, but it’s actually wrong

SELECT d.dept_no, d.emp_no, S. salary FROM dept_emp as D INNER JOIN salaries as s ON D.em_no = s.em_no WHERE d.to_date='9999-01-01 'AND s.to_date='9999-01-01' GROUP BY d.dept_no HAVING salary=MAX(s.salary);Copy the code
  • Error 1: d.ep_no is a non-aggregate field and cannot appear in SELECT. Because one aggregated field (dept_NO) corresponds to multiple non-aggregated fields (emp_NO), an error occurs when any of the non-aggregated fields is selected at random.
  • Error # 2: According to error # 1, if the above code is forced, it will not be detected when multiple people have the highest salary.
  • Mistake # 3: The HAVING statement is used in conjunction with the aggregate function to filter the set of records returned BY the GROUP BY statement. For example, HAVING AVG(score)>=80 filters groups with an AVG average greater than 80. HAVING salary=MAX(S.salary) is actually trying to filter the records in the group. This is wrong.

My answer

SELECT d1.dept_no, d1.emp_no, s1.salary
FROM dept_emp as d1
INNER JOIN salaries as s1
ON d1.emp_no=s1.emp_no
AND d1.to_date='9999-01-01'
AND s1.to_date='9999-01-01'
WHERE s1.salary in (SELECT MAX(s2.salary)
FROM dept_emp as d2
INNER JOIN salaries as s2
ON d2.emp_no=s2.emp_no
AND d2.to_date='9999-01-01'
AND s2.to_date='9999-01-01'
AND d2.dept_no = d1.dept_no
)
ORDER BY d1.dept_no;
Copy the code