LeetCode MySQL刷题——day3

目录

一、重新格式化部门表

1、题目描述

2、题解

3、源码

二、第二高的薪资

1、题目描述

2、题解

3、源码

 三、第n高的薪水

1、题目描述

2、题解

3、源码

 四、分数排名

1、题目描述

2、题解

3、源码

五、连续出现的数

1、题目描述

2、题解

 3、源码

---

一、重新格式化部门表

1、题目描述

 

2、题解

3、源码

# Write your MySQL query statement below
select id,
    sum(case month when 'Jan' then revenue end) as Jan_Revenue,
    sum(case month when 'Feb' then revenue end) as Feb_Revenue,
    sum(case month when 'Mar' then revenue end) as Mar_Revenue,
    sum(case month when 'Apr' then revenue end) as Apr_Revenue,
    sum(case month when 'May' then revenue end) as May_Revenue,
    sum(case month when 'Jun' then revenue end) as Jun_Revenue,
    sum(case month when 'Jul' then revenue end) as Jul_Revenue,
    sum(case month when 'Aug' then revenue end) as Aug_Revenue,
    sum(case month when 'Sep' then revenue end) as Sep_Revenue,
    sum(case month when 'Oct' then revenue end) as Oct_Revenue,
    sum(case month when 'Nov' then revenue end) as Nov_Revenue,
    sum(case month when 'Dec' then revenue end) as Dec_Revenue
from Department
group by id

二、第二高的薪资

1、题目描述

 

2、题解

 

3、源码

# Write your MySQL query statement below
# select ifNull(
#     (select distinct salary
#     from Employee
#     order by Salary Desc
#     limit 1,1),null
# ) as SecondHighestSalary;

select max(distinct salary) as SecondHighestSalary
from Employee
where salary < (select max(distinct salary)
                from Employee);


# select max(distinct 成绩) 
# from 成绩表
# where 课程='语文' and
#       成绩 < (select max(distinct 成绩) 
#               from 成绩表 
#               where 课程='语文');

 三、第n高的薪水

1、题目描述

 

2、题解

3、源码

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    SET N :=N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT 
      salary FROM Employee
      group by salary
      ORDER by  salary DESC
      LIMIT N,1
  ) ;
END

 四、分数排名

1、题目描述

 

 

2、题解

3、源码

select
    score,
    (dense_rank() over (order by Score desc)) AS "rank" 
from
    Scores 

五、连续出现的数

1、题目描述

2、题解

 3、源码

# Write your MySQL query statement below
select  distinct
    Num as ConsecutiveNums
from 
    (select Num,Id-cast((row_number() over(partition by Num order by Id asc)) as signed) as ranking from Logs) as t
group by Num,ranking
having count(*)>=3