题解 | #获取当前薪水第二多的员工的emp_no以及其对应的薪水salary#

So 1. In MySQL and SQL servers like PostgreSQL, AGG function and single columns cannot be selected at the same time unless the single columns are also in GROUP BY otherwise, it will get the error. So we could have only the AGG function selected and then it will take multiple inputs and give single output. This is why the code below is not working in MySQL: it says no GROUP BY used. but we see several single columns in the SELECT clause with MAX()

SELECT e.emp_no, MAX(s.salary), e.last_name, e.first_name
FROM employees AS e
INNER JOIN salaries AS s
ON e.emp_no=s.emp_no
WHERE salary<(SELECT MAX(salary) FROM salaries)

But the code above works in SQLlite it seems that SQLite has fewer limits than SQL. I do not know why... '

Correct commands: use nested WHERE to filter: second highest is the highest one excluding the highest one.

SELECT e.emp_no,salary, last_name, first_name FROM employees e 
INNER JOIN salaries s 
ON e.emp_no=s.emp_no
WHERE salary = (SELECT MAX(salary) FROM salaries
               WHERE salary<(SELECT MAX(salary) FROM salaries))
);

Extention:
Find the emp_no who has the third-highest or N-highest salary, return the emp_no and the counterpart salary:

SELECT e.emp_no, s.salary, e.last_name, e.first_name
FROM employees AS e
INNER JOIN salaries AS s
ON e.emp_no=s.emp_no
WHERE salary=
(SELECT s1.salary FROM salaries AS s1
INNER JOIN salaries AS s2
ON s1.salary <= s2.salary AND s1.to_date='9999-01-01' and s2.to_date='9999-01-01'
GROUP BY s1.salary
HAVING COUNT(DISTINCT s2.salary)=2
);

！！！ We can JOIN ON all comparision operators: =,<=,>=.. and logic operatoer: AND, OR NOT
!!!! the to_date='9999-01-01' is added to ensure all employees are in position. So it is okay to only have s1.salary <= s2.salary then we will have a inner joined table with each s1.salary might be linked to several s2.salary as long as the s1.salary is not the maximum one.
t.e.x salary: [700, 600, 500, 600, 400]
s1.salary ------------ s2.salary
700 -------------------- 700 : 1 as coount, 700 only less then 700
600 ---------------------700 : 3 as count, 600 less than 700, 600
600----------------------600
600----------------------600
500----------------------700: 4 as count, 500 less than 700,600,500
500----------------------600
500----------------------600
500----------------------500
400----------------------700: 5 as count 400 less than 700,600,500,400
...
AFTER The table is joinned and collected all info based on ON condition: s1.salary<=s2.salary
GROUP BY s1.salary
we did not apply AGG on GROUP BY so it is still list all s1.salary original data. No aggregation happens.

filter the GROUP BY category with HAVING COUNT(s2.salary). COUNT s2.salary per category here it is per s1.salary.

so here we apply COUNT on GROUP BY to filter, HAVING statement is filtering aggregation result in conjunction with GROUP BY.
DISTINCT removes the duplicative s2.salary that bigger than s1.salary. So as long as the count distinct number of s2.salary that bigger than s1.salary is 2, then this s1.salary is the second highest salary.

WHERE to_date='9999-01-01' this one has no big effect on filtering. it is adding for ensuring all data is in valid. This is why some solutions uses this one as ON in INNER JOINE but actually it is INNER JOIN by s1.salary<=s2.salary.

Slotuin 2:

SELECT e1.emp_no,t2.salary, e1.last_name, e1.first_name FROM employees e1,
(SELECT s1.emp_no, s1.salary FROM salaries AS s1
WHERE (SELECT COUNT(DISTINCT s2.salary) FROM salaries AS s2 
       WHERE s2.salary >= s1.salary)=2) AS t2
WHERE e1.emp_no=t2.emp_no 

Solution3:

SELECT e1.emp_no,s1.salary, e1.last_name, e1.first_name FROM employees e1
INNER JOIN salaries s1 
ON e1.emp_no=s1.emp_no 
WHERE (SELECT COUNT(DISTINCT s2.salary) FROM salaries AS s2 
       WHERE s2.salary >= s1.salary)=2