Leetcode SQL176~181

最新推荐文章于 2024-05-20 21:56:33 发布

逗×

最新推荐文章于 2024-05-20 21:56:33 发布

阅读量68

点赞数

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

本文链接：https://blog.csdn.net/peimina/article/details/129799774

版权

leetcode 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

176——查询第二高薪水

Employee 表：

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+
id 是这个表的主键。
表的每一行包含员工的工资信息。

编写一个 SQL 查询，获取并返回 Employee 表中第二高的薪水。如果不存在第二高的薪水，查询应该返回 null 。

查询结果如下例所示。

示例 1：

输入：
Employee 表：
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
输出：
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

示例 2：

输入：
Employee 表：
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
+----+--------+
输出：
+---------------------+
| SecondHighestSalary |
+---------------------+
| null                |
+---------------------+

select ifnull(
    (select distinct salary from Employee order by salary desc limit 1 offset 1),NULL) as SecondHighestSalary

IFNULL函数

IFNULL函数的语法：

IFNULL(expression_1,expression_2);

如果expression_1不为NULL，则IFNULL函数返回expression_1; 否则返回expression_2的结果。

IFNULL函数根据使用的上下文返回字符串或数字。

如果要返回基于TRUE或FALSE条件的值，而不是NULL，则应使用IF函数

limit函数

limit是限制的意思，用于限制返回的查询结果的行数 (可以通过limit指定查询多少行数据)（就是最后查询得到的结果是显示多少行）

offset

是跳过多少行然后开始去读

limit 2 offset 3 ：跳过3行开始读两行

limit 2,3：跳过两行，返回后面三行的数据

177——查询第n高薪水

表: Employee

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+
Id是该表的主键列。
该表的每一行都包含有关员工工资的信息。

编写一个SQL查询来报告 Employee 表中第 n 高的工资。如果没有第 n 个最高工资，查询应该报告为 null 。

查询结果格式如下所示。

示例 1:

输入: 
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
n = 2
输出: 
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+

示例 2:

输入: 
Employee 表:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
+----+--------+
n = 2
输出: 
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| null                   |
+------------------------+

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),null) as getNthHighestSalary
  );
END

178——按分数排序

表: Scores

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| score       | decimal |
+-------------+---------+
Id是该表的主键。
该表的每一行都包含了一场比赛的分数。Score是一个有两位小数点的浮点值。

编写 SQL 查询对分数进行排序。排名按以下规则计算:

分数应按从高到低排列。
如果两个分数相等，那么两个分数的排名应该相同。
在排名相同的分数后，排名数应该是下一个连续的整数。换句话说，排名之间不应该有空缺的数字。

按 score 降序返回结果表。

查询结果格式如下所示。

示例 1:

输入: 
Scores 表:
+----+-------+
| id | score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+
输出: 
+-------+------+
| score | rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

-- 利用开窗函数
select score,dense_rank() over(order by score desc) as 'rank'
from Scores

-- 不利用开窗
select s1.score,count(distinct(s2.score)) `rank`
from Scores s1,Scores s2 
where s1.score <= s2.score 
group by s1.Id 
order by `rank`

-- 首先我们确定了有多少种分数就有种多少种排名，而且排名是连续的，那么如果我想知道自己排多少名，就只需要知道我前面有多少种分数就好了，于是我们把分数去重一下，然后作为一个集合，再筛选一下大于自己的，就知道自己排名了。因为排名不能从0开始 ，所以都+1。
-- 比如3.50 s1没有比3.50大的，那么count(*)等于0 +1等于1
select s1.score,
(select count(*)+1 
from (select distinct score from Scores) s2 
where s2.score>s1.score) as `Rank` 
from Scores s1 order by `Rank`

180. 连续出现的数字

表：Logs

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| num         | varchar |
+-------------+---------+
id 是这个表的主键。

编写一个 SQL 查询，查找所有至少连续出现三次的数字。

返回的结果表中的数据可以按 任意顺序 排列。

查询结果格式如下面的例子所示：

示例 1:

输入：
Logs 表：
+----+-----+
| Id | Num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+
输出：
Result 表：
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+
解释：1 是唯一连续出现至少三次的数字。

# 方法一
select 
distinct num as ConsecutiveNums
from
(
    select 
        num, 
        # 从当前记录获取前一行记录的num值，如果没有前一行，则返回null
        lag(num,1,null) over (order by id) as lag_num,
        # 从当前记录获取后一行记录的num值，如果没有后一行，则返回null
        lead(num,1,null) over (order by id) as lead_num
    from logs
) as l
# 当前值等于前一行的值，以及等于后一行的值，即满足了至少出现三次
where l.num = l.lag_num and l.num = l.lead_num


# 方法二
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
与自关联或自连接相比，这种方法的效率更高，不受Logs表中的Id是否连续的限制，而且可以任意设定某个值连续出现的次数。
针对评论区朋友的疑问，这里做下解答：
1）逻辑：构建两个变量@prev 和@count ，前者用于与Num做比较判断，后者用于@prev和Num相等时的条件计数；
2）(select @prev := null,@count := null) as t 这句的作用是初始化两个变量，并将初始化后的变量放到一张临时表t中，:=符号在MySQL中是赋值的意思；
3）when @prev = Num then @count := @count + 1和when (@prev := Num) is not null then @count := 1 这两个语句不能交换顺序，赋值语句永远非NULL，所以一旦执行顺序来到了第二个when，@count 是一定会被赋值为1的，后者放到前面的话就达不到计数的目的；
4）(@prev := Num) is not null这部分去掉后面加的判断，SQL也能正常执行，上面SQL中case when的这种用法，when后是判断条件，赋值后又加判断，我原以为这样会好理解点；
5）case when本质是一个函数，有值时就返回内部处理得到的值，无值就返回NULL，针对每一个Num，上面SQL中的case when 都会有一个计数，并把这个计数返回给CNT。
PS：MySQL8.0以后的版本开始支持窗口函数，使用窗口函数也能很好的解决此类问题。关于MySQL的窗口函数，可以参考译文：https://blog.csdn.net/qq_41080850/article/details/86416106

# 方法三
# 1. 三表直接连接
select distinct l1.num as ConsecutiveNums
from Logs as l1, Logs as l2, Logs as l3
where l1.id = l2.id - 1 and l1.id = l3.id - 2 and l1.num = l2.num and l2.num = l3.num


# 方法四
# 2. 利用窗口函数求出diff后进行分组，再用having子句过滤
select distinct num as ConsecutiveNums
from (select num, id + 1 - row_number() over(partition by num order by id) as diff
      from Logs) t
group by num, diff
having count(*) >= 3


# 方法五
# 3. 如果id不是连续的，要求按照表中连续3个以上的记录
select distinct num as ConsecutiveNums
from (select num, (row_number() over(order by id) - row_number() over(partition by num order by id)) as diff
      from Logs) t
group by num, diff
having count(*) >= 3

lag(expr,N,default)

**expr:**它可以是列或任何内置函数。
**N:**它是一个正值，它确定当前行之前的行数。如果在查询中将其省略，则其默认值为1
**default:**如果在当前行之前没有行N行的情况下，它是函数返回的默认值。如果缺少，则默认为NULL。

lead(expr,N,default)

**expr:**它可以是列或任何内置函数。
**N:**它是一个正值，它确定当前行之后的行数。如果在查询中将其省略，则其默认值为1
**default:**如果在当前行之后没有行N行的情况下，它是函数返回的默认值。如果缺少，则默认为NULL。

181——超过经理收入的员工

Employee
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
| salary      | int     |
| managerId   | int     |
+-------------+---------+
Id是该表的主键。
该表的每一行都表示雇员的ID、姓名、工资和经理的ID。

编写一个SQL查询来查找收入比经理高的员工。

以 任意顺序 返回结果表。

查询结果格式如下所示。

示例 1:

输入: 
Employee 表:
+----+-------+--------+-----------+
| id | name  | salary | managerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | Null      |
| 4  | Max   | 90000  | Null      |
+----+-------+--------+-----------+
输出: 
+----------+
| Employee |
+----------+
| Joe      |
+----------+
解释: Joe 是唯一挣得比经理多的雇员。

select e1.name as Employee
from Employee e1 join Employee e2 on e1.managerId = e2.id
where e1.salary > e2.salary

逗×

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Leetcode SQL176~181

leetcode sql
复制链接

扫一扫

专栏目录

Leetcode SQL176~181

176——查询第二高薪水

177——查询第n高薪水

178——按分数排序

180. 连续出现的数字

181——超过经理收入的员工

“相关推荐”对你有帮助么？