Train of thought

These types of questions are often accompanied by subqueries, sorting applications, individual questions with NULL values, and other functions.

(Note: the following types of questions and part of the solution ideas are transferred to self-reliance (LeetCode), friends who need to find on the force to find online)

Purely in terms of salary

• Diverge from the simplest dimension — the highest salary expands

1. Query the second highest salary

1. The highest salary is not in the highest salary. 2

Code: Idea 1

select max(salary) fromThe table where the notin(Subquery maximum salary)Copy the code

Idea 2

select (distinct salary) fromOrder by salary desc limit1.1Add the ifNULL function #select IFNULL ((select DISTINCT Salary)fromOrder by Salary desc # limit 11.1),null) as SecondHighestSalar
Copy the code

(Note to remove the weight here)

Query the NTH highest salary

Compared with the first question, the applicability of train of thought 1 is worse, so train of thought 2 is adopted: sort and query. However, this train of thought has defects, which is suitable for global query, and can not use the way of thinking about self-linking and sorting function when there are repeated values

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    SET N := N-1;
  RETURN (
      select  salary fromOrder by salary desc limit N,1
  );
END
Copy the code

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
        SELECT 
            DISTINCT salary
        FROM 
            (SELECT 
                salary, dense_rank() over(ORDER BY salary DESC) AS rnk
             FROM 
                employee) tmp
        WHERE rnk = N
  );
END
Copy the code

In terms of department and salary

• In this kind of questions, an added dimension, namely, to divide by department

The highest paid employee in the department

Group by or where: leetcode-cn.com/problems/de…

Then we can use the group WHERE to query (note the Join method, why not use left Join) :

select 
    Department.Name as Department,
    Employee.Name as Employee,
    Salary
from Employee join Department on Department.Id = Employee.DepartmentId
where (Employee.DepartmentId,Salary) in 
        (select  
            DepartmentId,
            max(Salary)
            from Employee 
            group by DepartmentId
        )
Copy the code

All the top three employees in the department

Topic: leetcode-cn.com/problems/de… Create a subquery. 2. Create a subquery by group. 3. Direct screening

select 
    Department,
    Employee,
    Salary
from 
    (select 
        Department.Name as 'Department',
        Employee.name as 'Employee',
        Salary,
        dense_rank() over(partition by DepartmentId order by Salary desc ) as ranking
        from
        Employee inner join Department on Employee.DepartmentId = Department.Id
    )  as t
where ranking <= 3
Copy the code

conclusion

This kind of questions, need to make full use of sorting, grouping ideas, the direction of entry is also very important, whether to screen out the high, or directly looking for the low; Use where group query directly, or use sorting method, treat different cases can not be generalized; Self – link or sub – query ideas also have their advantages. Finally, the above questions are extracted from the force button, while the force button official website and the forum have given a different way to solve the problem. The near excerpt is the most close to the author’s own ideas, only for reference.