185. Department Top Three Salaries

topic:

Table employee has all employee information, including ID, salary, and department ID.

Id	Name	Salary	DepartmentID
1	Joe	70000	1
2	Henry	80000	2
3	Sam	60000	2
4	Max	90000	1
5	Janet	69000	1
6	Randy	85000	1

Company Department Information Table Department

Id	Name
1	IT
2	Sales

Find out the employee information in the top three of each department's salary line. If the salary is the same, tie it.
For example:

Department	Employee	Salary
IT	Max	90000
IT	Randy	85000
IT	Joe	70000
Sales	Henry	80000
Sales	Sam	60000

parsing:

There are two tables in the question, the first step is to connect.

Select 
    dd. Name as Department, 
    ee. Name as Employee,
    ee. Salary as Salary
from Employee ee, Department dd
where EE. DepartmentID =dd.id ... 

Mark the correct header with AS.
Next is the conditional part, although it seems to be grouped (top three by department) but if using group by to take the first three is really difficult, using group by is a good way to control, so you can use a clever method here, this method is also applicable to the " Find the top (low) N-item category problem.

(
Select 
    count (Distinct ee2.salary) 
from 
    employee ee2 
where 
    ee. Departmentid=ee2. DepartmentID and Ee.salary<ee2.salary
) <=2

Ideas for, the employee do self connection. Notice how the connection is here, EE. Departmentid=ee2. DepartmentID, this is not a one-to-one relationship between the rows of data in the two tables, because DepartmentID repeats multiple and does not have a unique identity. This allows each employee to form multiple data rows with all employees in their department.
Take the individual example:

Age Age

name	Sex		Tel	name	Sex		Tel
Jojo	Man	30	17754585545	Jojo	Man	30	17754585545
Tom	Man	54	11012121122	Jojo	Man	30	17754585545
Harry	Man	18	17722454565	Jojo	Man	30	17754585545
Zhao Liu	Man	18	18922454565	Jojo	Man	30	17754585545
Sun Seven	Woman	30	10522454565	Sun Seven	Woman	30	10522454565
John doe	Woman	20	13522454565	Sun Seven	Woman	30	10522454565

This is a sex-disaggregated class that shows that weeks and all other men make up 4 rows of data.

This is the way the table is joined, and the way it is retrieved is
Ee.salary<ee2.salary.
When we get a table in the form of a previous example, the size of each class is easy to compare (because there is already a row of data for all the combinations between the peers).

If a ee.salary employee's salary is the highest of its kind, then after
Ee.salary<ee2.salary
This comparison will find that there is no one employee whose salary is higher than the maximum salary, then the total number of the select count () is 0.
Similarly, if the employee's salary is high, then only one person's salary is higher than his, so select count () gets the number 1.
It's not hard to see that the third highest-paying employee was counted at 2.
Therefore, as long as the Select count () count is less than or equal to 2, the first three high numbers.

Note: It is important to note that the actual use of select count (distinct salary) is mainly due to the salary of the same rank. As a result, there are no more than 3 of the top three, possibly 4 (with two salaries tied) or more.

To sum up, the final SQL lookup statement is:

Select 
    dd. Name as Department, 
    ee. Name as Employee,
    ee. Salary as Salary
from Employee ee, Department dd
where EE. DepartmentID =dd.id 
and
    (
    Select 
        count (Distinct ee2.salary) 
    from 
        employee Ee2 
    where 
        EE. Departmentid=ee2. DepartmentID and Ee.salary<ee2.salary
    ) <=2