SQL实现LeetCode(176.第二高薪水)

[LeetCode] 176.Second Highest Salary 第二高薪水

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.

这道题让我们找表中某列第二大的数,这道题有很多种解法,先来看一种使用Limit和Offset两个关键字的解法,MySQL中Limit后面的数字限制了我们返回数据的个数,Offset是偏移量,那么如果我们想找第二高薪水,我们首先可以先对薪水进行降序排列,然后我们将Offset设为1,那么就是从第二个开始,也就是第二高薪水,然后我们将Limit设为1,就是只取出第二高薪水,如果将Limit设为2,那么就将第二高和第三高薪水都取出来:

解法一:

SELECT Salary FROM Employee GROUP BY Salary
UNION ALL (SELECT NULL AS Salary)
ORDER BY Salary DESC LIMIT 1 OFFSET 1;

我们也可以使用Max函数来做,这个返回最大值,逻辑是我们取出的不包含最大值的数字中的最大值,即为第二大值:

解法二:

SELECT MAX(Salary) FROM Employee
WHERE Salary NOT IN
(SELECT MAX(Salary) FROM Employee);

下面这种方法和上面基本一样,就是用小于号<代替了Not in关键字,效果相同:

解法三:

SELECT MAX(Salary) FROM Employee
Where Salary <
(SELECT MAX(Salary) FROM Employee);

最后来看一种可以扩展到找到第N高的薪水的方法,只要将下面语句中的1改为N-1即可,第二高的薪水带入N-1就是1,下面语句的逻辑是,假如我们要找第二高的薪水,那么我们允许其中一个最大值存在,然后在其余的数字中找出最大的,即为整个的第二大的值;

解法四:

SELECT MAX(Salary) FROM Employee E1
WHERE 1 =
(SELECT COUNT(DISTINCT(E2.Salary)) FROM Employee E2
WHERE E2.Salary > E1.Salary);

参考资料:

https://leetcode.com/discuss/47041/very-very-simple-solution

https://leetcode.com/discuss/42849/general-solution-not-using-max

https://leetcode.com/discuss/21751/simple-query-which-handles-the-null-situation

到此这篇关于SQL实现LeetCode(176.第二高薪水)的文章就介绍到这了,更多相关SQL实现第二高薪水内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • C++实现LeetCode(170.两数之和之三 - 数据结构设计)

    [LeetCode] 170. Two Sum III - Data structure design 两数之和之三 - 数据结构设计 Design and implement a TwoSum class. It should support the following operations: add and find. add - Add the number to an internal data structure. find - Find if there exists any pai

  • SQL实现LeetCode(175.联合两表)

    [LeetCode] 175.Combine Two Tables 联合两表 Table: Person +-------------+---------+ | Column Name | Type    | +-------------+---------+ | PersonId    | int     | | FirstName   | varchar | | LastName    | varchar | +-------------+---------+ PersonId is the

  • C++实现LeetCode(173.二叉搜索树迭代器)

    [LeetCode] 173.Binary Search Tree Iterator 二叉搜索树迭代器 Implement an iterator over a binary search tree (BST). Your iterator will be initialized with the root node of a BST. Calling next() will return the next smallest number in the BST. Note: next() and

  • SQL实现LeetCode(177.第N高薪水)

    [LeetCode] 177.Nth Highest Salary 第N高薪水 Write a SQL query to get the nth highest salary from the Employee table. +----+--------+ | Id | Salary | +----+--------+ | 1  | 100    | | 2  | 200    | | 3  | 300    | +----+--------+ For example, given the ab

  • C++实现LeetCode(171.求Excel表列序号)

    [LeetCode] 171.Excel Sheet Column Number 求Excel表列序号 Related to question Excel Sheet Column Title Given a column title as appear in an Excel sheet, return its corresponding column number. For example:     A -> 1 B -> 2 C -> 3 ... Z -> 26 AA -&g

  • C++实现LeetCode(169.求大多数)

    [LeetCode] 169. Majority Element 求大多数 Given an array nums of size n, return the majority element. The majority element is the element that appears more than ⌊n / 2⌋ times. You may assume that the majority element always exists in the array. Example 1

  • C++实现LeetCode(172.求阶乘末尾零的个数)

    [LeetCode] 172. Factorial Trailing Zeroes 求阶乘末尾零的个数 Given an integer n, return the number of trailing zeroes in n!. Example 1: Input: 3 Output: 0 Explanation: 3! = 6, no trailing zero. Example 2: Input: 5 Output: 1 Explanation: 5! = 120, one trailing

  • SQL实现LeetCode(178.分数排行)

    [LeetCode] 178.Rank Scores 分数排行 Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, th

  • SQL实现LeetCode(176.第二高薪水)

    [LeetCode] 176.Second Highest Salary 第二高薪水 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

  • SQL实现LeetCode(184.系里最高薪水)

    [LeetCode] 184.Department Highest Salary 系里最高薪水 The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id. +----+-------+--------+--------------+ | Id | Name  | Salary | DepartmentId

  • SQL实现LeetCode(185.系里前三高薪水)

    [LeetCode] 185.Department Top Three Salaries 系里前三高薪水 The Employee table holds all employees. Every employee has an Id, and there is also a column for the department Id. +----+-------+--------+--------------+ | Id | Name  | Salary | DepartmentId | +--

  • SQL实现LeetCode(181.员工挣得比经理多)

    [LeetCode] 181.Employees Earning More Than Their Managers 员工挣得比经理多 The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id. +----+-------+--------+-----------+ | Id | Na

  • SQL实现LeetCode(180.连续的数字)

    [LeetCode] 180.Consecutive Numbers 连续的数字 Write a SQL query to find all numbers that appear at least three times consecutively. +----+-----+ | Id | Num | +----+-----+ | 1  |  1  | | 2  |  1  | | 3  |  1  | | 4  |  2  | | 5  |  1  | | 6  |  2  | | 7  |

  • SQL实现LeetCode(197.上升温度)

    [LeetCode] 197.Rising Temperature 上升温度 Given a Weather table, write a SQL query to find all dates' Ids with higher temperature compared to its previous (yesterday's) dates. +---------+------------+------------------+ | Id(INT) | Date(DATE) | Temperat

  • SQL实现LeetCode(183.从未下单订购的顾客)

    [LeetCode] 183.Customers Who Never Order 从未下单订购的顾客 Suppose that a website contains two tables, the Customers table and the Orders table. Write a SQL query to find all customers who never order anything. Table: Customers. +----+-------+ | Id | Name  |

随机推荐