2.SQL

一.第二高薪水

要点：去重，null

第二高薪水

#解法一：循环查询
SELECT
    (SELECT DISTINCT
            Salary
        FROM
            Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1) AS SecondHighestSalary

#解法二：ifnull判断
SELECT
    IFNULL(
      (SELECT DISTINCT Salary
       FROM Employee
       ORDER BY Salary DESC
        LIMIT 1 OFFSET 1),
    NULL) AS SecondHighestSalary

二、第N高薪水

要点：参数

第N高薪水

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
        set n = n-1;
  RETURN (
      select ifnull(
          (select distinct Salary from Employee
           order by salary desc
           limit n , 1
              
        )
        ,null) as getNthHighestSalary
      
  );
END

三、分数排名

要点：变量，嵌套循环

分数排名

利用@a变量的作为基数，@pre判断分数是否发生改变（改变为1，未变为0），循环得到Rank
select Score,@a := @a+(@pre<>(@pre := Score)) as Rank from Scores,
(select @a := 0,@pre := 0)t
order by Score desc

将大小比较结果作为子查询返回为Rank，注意对子查询的Score去重。
select a.Score,
(select  count(distinct b.score) from Scores as b where (a.score<=b.score)) as Rank
from Scores as a
order by Score desc

四、连续出现的数字

要点：自连接，将本身一张表复制为多张相同的表来使用。

连续数字

select DISTINCT L1.NUM AS ConsecutiveNums from 
logs as L1,
logs as L2,
logs as L3
where
L1.ID = L2.ID-1
AND
L2.ID = L3.ID-1
AND
L1.NUM=L2.NUM
AND
L2.NUM=L3.NUM

五、超过经理收入的员工

要点：自连接，内连接

超过经理收入的员工

select L1.name as Employee 
from 
Employee as L1,
Employee as L2
where
L1.ManagerId=L2.Id
and
L1.Salary>L2.Salary

SELECT a.NAME AS Employee
FROM 
Employee AS a 
JOIN Employee AS b
ON a.ManagerId = b.Id
AND 
a.Salary > b.Salary