MYSQL 相关题目

1.第二高的薪水
±—±-------+
| Id | Salary |
±—±-------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 | 输出第二高薪水，没有则输出NULL。
±—±-------+
重点：采用ifnull语句 不存在则输出null

select 
ifnull
((select distinct Employee.Salary 
from Employee 
order by Salary 
desc limit 1,1),null)
as SecondHighestSalary;

2.分数 排名

select score,(dense_rank() over(order by score desc)) as 'Rank' from Scores;

并列跳跃排名 rank() over(排序方式)
并列连续排名 dense_rank() over
3.连续出现三次的ID

SELECT DISTINCT
    l1.Num AS ConsecutiveNums
FROM
    Logs l1,
    Logs l2,
    Logs l3
WHERE
    l1.Id = l2.Id - 1
    AND l2.Id = l3.Id - 1
    AND l1.Num = l2.Num
    AND l2.Num = l3.Num
;

row_number() over([partition by value_expression,…n] order by columnName)

partition by:分区函数
3. 部门工资最高的员工

SELECT DISTINCT Num FROM (
SELECT Num,COUNT(1) as SerialCount FROM 
(SELECT Id,Num,
row_number() over(order by id) -
ROW_NUMBER() over(partition by Num order by Id) as SerialNumberSubGroup
FROM ContinueNumber) as Sub
GROUP BY Num,SerialNumberSubGroup HAVING COUNT(1) >= 3) as Result

# Write your MySQL query statement below
select Department.name as 'Department',Employee.name as 'Employee', Employee.salary as 'Salary'
from Employee 
join Department 
on Employee.departmentid=Department.id 
where(departmentid, salary) in
(
    select 
    departmentid,max(salary) 
    from  Employee 
    group by departmentid
);