176 - Second Highest Salary

最新推荐文章于 2020-11-09 10:51:34 发布

原创最新推荐文章于 2020-11-09 10:51:34 发布 · 693 阅读

0 ·

CC 4.0 BY-SA版权

LeetCode 专栏收录该内容

41 篇文章

订阅专栏

本文介绍如何使用SQL从员工表中获取第二高的薪资。提供两种方法实现这一目标，一种是通过子查询找到最高薪资后再筛选次高薪资；另一种是直接对薪资进行排序并选取次高位。

Write a SQL query to get the second highest salary from the Employee table.

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

For example, given the above Employee table, the second highest salary is 200. If there is no second highest salary, then the query should return null.

Subscribe to see which companies asked this question

思路分析：

解法一：

select Max(Salary) from Employee where Salary < (select Max(Salary) from Employee)

解法二：

SELECT (SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1,1)

确定要放弃本次机会？

福利倒计时

: :

立减 ¥

普通VIP年卡可用

立即使用

hellochenlu

关注关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
分享

复制链接

分享到 QQ

分享到新浪微博

扫一扫
举报

举报

专栏目录

176. Second Highest Salary

Smile&搁浅

06-06

1138

Write a SQL query to get the second highest salary from the Employee table. +----+--------+ | Id | Salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+ For exa

Leetcode每日一题：176.second-highest-salary(第二高的薪水)

CodeLuweir 的博客

08-23

501

1.解法一：利用 limit 进行限制此方法可适用于求第N高的薪水，且数据越复杂，速度优势越明显； limit 的用法为：select * from tableName limit i,n tableName：表名 i：为查询结果的索引值(默认从0开始)，当i=0时可省略i n：为查询结果返回的数量 i与n之间使用英文逗号","隔开代码为： select (select distinct salary from employee order by salary desc lim..

参与评论您还未登录，请先登录后发表或查看评论

LeetCode 176. Second Highest Salary第二高薪水（数据库）

qq_41878358的博客

02-13

295

题目：解答：解法一：分两部分考虑，第一部分是最高的薪水，第二部分是比最高薪水小的最高的薪水，即第二高薪水 select Max(Salary) SecondHighestSalary from Employee where Salary<(select Max(Salary) from Employee) 解法二： select IFNULL( (select Salar...

【LeetCode】Second Highest Salary && Nth Highest Salary

wangzq的专栏

01-16

3509

1、Second Highest Salary Total Accepted: 1030 Total Submissions: 4309 My Submissions Question Solution Write a SQL query to get the second highest salary from the Employee table. +----+--------+ |

LeetCode：Second Highest Salary - 第二高的工资

08-16

196

1、题目名称 Second Highest Salary（第二高的工资） 2、题目地址 https://leetcode.com/problems/second-highest-salary/ 3、题目内容现在有一张记录了Id（主键）和Salary（工资）的表，求出其中第二高的工资...

SELECT DISTINCT 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 ; 这两段代码有什么区别，为什么一个可以在只有一条记录时返回null，一个不行

04-01

SELECT DISTINCT Salary AS SecondHighestSalary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1 这个查询直接从Employee表中选择不同的Salary，按降序排列，跳过第一条，取第二条。如果表中只有一条记录，...

Employee 表： +-------------+------+ | Column Name | Type | +-------------+------+ | id | int | | salary | int | +-------------+------+ id 是这个表的主键。表的每一行包含员工的工资信息。查询并返回 Employee 表中第二高的不同薪水。如果不存在第二高的薪水，查询应该返回 null(Pandas 则返回 None) 。查询结果如下例所示。示例 1：输入： Employee 表： +----+--------+ | id | salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+ 输出： +---------------------+ | SecondHighestSalary | +---------------------+ | 200 | +---------------------+ 示例 2：输入： Employee 表： +----+--------+ | id | salary | +----+--------+ | 1 | 100 | +----+--------+ 输出： +---------------------+ | SecondHighestSalary | +---------------------+ | null | +---------------------+

11-25

SELECT max( Salary ) SecondHighestSalary FROM Employee WHERE Salary (SELECT max( Salary )FROM Employee); ``` 此方法先通过子查询找出最高薪水，然后在主查询中筛选出小于最高薪水的记录，最后取这些记录中...

select ifnull( (select distinct salary from Employee order by salary desc limit 1 offset 1 ),null) as SecondHighestSalary 讲解一下

03-28

) AS SecondHighestSalary; 我的任务是逐步分析这个查询的工作原理，并解释为什么它能找到第二高的薪水。让我先拆解每个部分。首先，内部的子查询部分：(SELECT DISTINCT salary FROM Employee ORDER BY salary ...

with t1 as ( select salary, dense_rank() over ( order by salary desc ) top from employee ) select max(salary) SecondHighestSalary from t1 where top = 2; 求第二高的薪水

最新发布

11-25

你写的 SQL 查询是**正确的思路**，用于查询“第二高薪水”（SecondHighestSalary），使用了 `DENSE_RANK()` 和 CTE（公用表表达式）来实现。但需要注意一些边界情况：如果不存在第二高的薪水（比如所有人薪水都一样...

select ifnull((select salary from Employee order by salary desc limit 1,1),null) as SecondHighestSalary 解释一下为啥select 后面不用加from、

07-06

select IFNULL((select salary from Employee order by salary desc limit 1,1), null) as SecondHighestSalary ``` ### 输出 - 如果有第二高的工资，返回该工资。 - 如果没有第二高的工资，返回 `NULL`。

SQL 176. Second Highest Salary

同销万古愁的博客

11-09

323

目录总结题目解法一解法二 rank解法（wrong）总结找第二高的薪水，没有第二高的薪水时，返回null。我用了rank()函数，但是当没有第二高的薪水时，返回的是空，不符合要求。看了下解决方案，个人感觉最好的是用limit或limit+offset来做 limit n,m，意为跳过开头的n位，从第n+1位开始取，一共取m个。 limit n offset m，意为跳过开头的m位，从第m+1开始取，一共取n个。题目 Write a SQL query to ge.

176. Second Highest Salary（Leetcode)

卷毛的博客

11-03

338

文章出处：http://www.cnblogs.com/grandyang/p/5348961.html Write a SQL query to get the second highest salary from the Employee table. +----+--------+ | Id | Salary | +----+--------+ | 1 | 100 | | 2

【Leetcode】176. Second Highest Salary

数学、算法爱好者的博客

09-25

289

题目地址： https://leetcode.com/problems/second-highest-salary/ 给定一个Employee表如下： +----+--------+ | Id | Salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+ 要求返回第二高的Salary，例如，要返回： +---------------------+ | SecondHighestSalar

leetcode 176. Second Highest Salary

星尘

06-05

1468

Write a SQL query to get the second highest salary from the Employee table. +----+--------+ | Id | Salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+ For exa

LeetCode Second Highest Salary

在路上的学习者

08-18

2009

取第二大的值。

Second Highest Salary

琼华

04-30

3313

Write a SQL query to get the second highest salary from the Employee table. +-