目录

​​一、重新格式化部门表​​

​​1、题目描述​​

​​2、题解​​

​​3、源码​​

​​二、第二高的薪资​​

​​1、题目描述​​

​​2、题解​​

​​3、源码​​

​​ 三、第n高的薪水​​

​​1、题目描述​​

​​2、题解​​

​​3、源码​​

​​ 四、分数排名​​

​​1、题目描述​​

​​2、题解​​

​​3、源码​​

​​五、连续出现的数​​

​​1、题目描述​​

​​2、题解​​

​​ 3、源码​​

一、重新格式化部门表

1、题目描述

LeetCode MySQL刷题——day3_mysql

LeetCode MySQL刷题——day3_蓝桥杯_02

 

2、题解

LeetCode MySQL刷题——day3_蓝桥杯_03

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、题目描述

LeetCode MySQL刷题——day3_leetcode_04

LeetCode MySQL刷题——day3_leetcode_05

 

2、题解

LeetCode MySQL刷题——day3_mysql_06

 

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、题目描述

LeetCode MySQL刷题——day3_mysql_07

 

LeetCode MySQL刷题——day3_蓝桥杯_08

2、题解

LeetCode MySQL刷题——day3_蓝桥杯_09

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、题目描述

LeetCode MySQL刷题——day3_蓝桥杯_10

 

LeetCode MySQL刷题——day3_mysql_11

 

2、题解

LeetCode MySQL刷题——day3_leetcode_12

3、源码

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

五、连续出现的数

1、题目描述

LeetCode MySQL刷题——day3_leetcode_13

LeetCode MySQL刷题——day3_mysql_14

2、题解

LeetCode MySQL刷题——day3_leetcode_15

LeetCode MySQL刷题——day3_leetcode_16

 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