leetcode上的数据库题汇总(1)

最新推荐文章于 2023-12-04 14:21:33 发布

原创最新推荐文章于 2023-12-04 14:21:33 发布 · 841 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #sql #leetcode

LeetCode刷题练习同时被 3 个专栏收录

18 篇文章

订阅专栏

leetcode做题代码合集

18 篇文章

订阅专栏

mysql数据库总结

6 篇文章

订阅专栏

本文精选了LeetCode上的经典SQL挑战题，包括表格数据整合、获取次高薪资、找出第N高薪资、成绩排名及连续数字的查询。通过实际案例，深入解析SQL查询技巧，帮助读者掌握复杂查询的编写方法。

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

Combine Two Tables

https://leetcode.com/problems/combine-two-tables/

 select a.FirstName,a.LastName,b.City,b.State from Person a LEFT JOIN Address b on a.PersonId=b.PersonId

Second Highest Salary

https://leetcode.com/problems/second-highest-salary/

 select MAX(Salary) as SecondHighestSalary from Employee where Salary < (select MAX(Salary) from Employee)

Nth Highest Salary

https://leetcode.com/problems/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 M,1
  );
END

Rank Scores

https://leetcode.com/problems/rank-scores/

SELECT S.Score, COUNT(DISTINCT T.Score) AS Rank
FROM Scores S 
JOIN Scores T ON S.Score <= T.Score
GROUP BY S.Id
ORDER BY S.Score DESC

Consecutive Numbers

https://leetcode.com/problems/consecutive-numbers/

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