LeetCode力扣数据库题库-中等难度模式部分题解

最新推荐文章于 2023-02-22 13:30:02 发布

原创

最新推荐文章于 2023-02-22 13:30:02 发布 · 1.2k 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #sql

177.第N高薪水

在这里插入图片描述

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N=N-1;#用户变量
	 RETURN (
      # Write your MySQL query statement below.
      select ifnull(
	      (select distinct Salary 
	      from Employee
	      order by Salary desc
	      limit N,1)#limit N-1,1错误，limit后面不能带运算符，只能是常量
      ,null) getNthHighestSalary(N) 
     
      );
END

178.分数排名

在这里插入图片描述

select a.Score,count(distinct (b.Score)) as 'Rank'
from Scores a
join Scores b
on a.Score<=b.Score
group by a.Id
order by a.Score desc;

180.连续出现的数字

在这里插入图片描述
方式一

select distinct a.Num ConsecutiveNums
from Logs a
join Logs b
on a.Id=b.Id-1
join Logs c
on b.Id=c.Id-1
where a.Num=b.Num and b.Num=c.Num;

方式二

/*
select Num, 
    case 
      when @prev = Num then @count := @count + 1 #比较
      when (@prev := Num) is not null then @count := 1 #赋值1
    end as CNT
  from Logs, (select @prev := null,@count := null) as t #初始化变量
   #先将Num赋值给@prev，然后判断@prev是否为null，若是，则再把1赋值给@prev
   #@prev是用户变量 =是比较相等，:=是赋值
   */
   select distinct Num as ConsecutiveNums
from (
  select Num, 
    case 
      when @prev = Num then @count := @count + 1
      when (@prev := Num) is not null then @count := 1
    end as CNT
  from Logs, (select @prev := null,@count := null) as t
) as temp
where temp.CNT >= 3

184.部门工资最高的员工

在这里插入图片描述

步骤1：查找各个部门最大工资值
步骤2：在1的基础上找到对应部门名和员工名

select  d.Name Department,e.Name Employee,m.Salary
from (select max<

最低0.47元/天解锁文章