leetcode 数据库刷题

最新推荐文章于 2023-12-04 14:17:24 发布

sweet_sauce

最新推荐文章于 2023-12-04 14:17:24 发布

阅读量889

点赞数

CC 4.0 BY-SA版权

分类专栏： leetcode 文章标签： sql

本文链接：https://blog.youkuaiyun.com/sinat_33641359/article/details/79810055

leetcode 专栏收录该内容

4 篇文章

订阅专栏

本文针对MySQL面试中常见的几个题目进行实战解析，包括左连接、第二高薪水查询、第N高薪水查询及成绩排名等，通过具体代码示例帮助读者理解和掌握相关知识点。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

数据库比较薄弱，基本啥都不会，刷题提高一下，写篇博客记录一下。ps:leetcode appreciate

175. Combine Two Tables简单的左联问题

# Write your MySQL query statement below
select Person.FirstName , Person.LastName , Address.City , Address.State from Person left join Address on Person.PersonId = Address.PersonId

# left right inner 都要了解以及熟悉

176. Second Highest Salary

# Write your MySQL query statement below
# select  NULLIF(Salary, '')  as SecondHighestSalary 
# from Employee 
# order by Salary desc Limit 1 offset 1
# 为什么我这个不行？？
select (
  select distinct Salary from Employee order by Salary Desc limit 1 offset 1
)as SecondHighestSalary

# distinct 遇到空字符串，默

177. Nth Highest Salary

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
declare m int ;
set m = N - 1 ;
  RETURN (
      # Write your MySQL query statement below.
      select distinct Salary from Employee order by Salary desc limit 1 offset m
  );
END

# 注意limit offset 用法 limit n ; limit n , m ; limit n offset m ; 这几个用法的区别

178. Rank Scores

# Write your MySQL query statement below
# select score , (@i := @i + 1) from Scores,(select @i := 0) init order by score desc

# SELECT
#   Score,
#    @rank := @rank + (@prev != (@prev := Score))  as Rank 
#    # 这个Rank起到重命名作用 <> 和 != 相等的 不等的话就会返回1
# FROM
#   Scores,
#   (SELECT @rank := 0, @prev := -1) init
# #   用SELECT 来给变量赋值 初始化
# ORDER BY Score desc 

select 
    Score , @rank := @rank + (@pre != (@pre := Score)) as Rank
from
    Scores ,
    (select @rank := 0 , @pre := -1) init 
order by Score desc