秋招学习数据库LeetCode刷题

数据库基本知识以前学过次数较多，今天看完一遍后都是可以理解的。直接刷Leetcode题吧
牛客上题库刷基础，Leetcode刷 写语句题(争取坚持每日2个sql语句题)

牛客：https://www.nowcoder.com/exam/intelligent?questionJobId=10&tagId=21015
Leetcode：https://leetcode.cn/problems/second-highest-salary/description/

20240403

1.第二高的薪水

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

2.第N高薪水

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT; 
    SET M = N-1; 
  RETURN (
      SELECT DISTINCT salary
      FROM Employee
      ORDER BY salary DESC
      LIMIT M, 1
  );
END

美妙！！！limit这个函数是想不到是这样用，思路灵活好重要

2024.04.06（清明放假玩了2天）

178.分数排名

select score,dense_rank() over(order by score desc) as "rank"
from Scores
order by score desc;

180.连续出现的数字

select
    distinct t.num as ConsecutiveNums 
from
(
    select 
        id,
        num,
        row_number() over(order by id) as rn,
        row_number() over(partition by num order by id) as id_rn
    from Logs 
) t
group by t.num, (t.rn - t.id_rn)
having count(1) >= 3
;

20240407

10个牛客sql题

181.超过经理收入的员工

select  
    a.Name as 'Employee' From  
       Employee AS a,     Employee AS b where  
          a.ManagerId=b.Id          and a.Salary > b.Salary;

182.查找重复的电子邮箱

第一想法
耗时长

select Email  from Person group by  Email having count(Email)>1

select Email 
from (   select Email, count(Email) as num   from Person   group by Email ) 
as statistic 
where num > 1 ;