编写一个 SQL 查询,获取 Employee 表中第二高的薪水(Salary) 。 Id Salary 1 100 2 200 3 300 例如上述 Employee 表,SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水,那么查询应返回 null。 SecondHighestSalary 200 分析 首先看到这个题目,最先想到的是查询最高的薪水,只需要降序排序,然后取...
找第二大的数SQL-Second Highest Salary 1: 找小于最大的最大的 1 select max(Salary) from Employee where Salary<(select MAX(Salary) from Employee); 2. 排序 1 select Salary from Employee where Salary not in (select MAX(Salary)from Employee) order by Salary desc limit 1; 1 2 3 4 5 ...
或者 select max(Salary) as SecondHighestSalary from Employee where Salary not in (select max(Salary) from Employee) 思路二: SELECT IFNULL( (SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1), NULL) AS SecondHighestSalary 知识点总结: limit offset 使用 SELECTcolumn_list...
+---+---+ For example, given the above Employee table, the second highest salary is200. If there is no second highest salary, then the query should returnnull. https://leetcode.com/problems/second-highest-salary/ 首先MySQL不支持top语法,要用limit代替。 selectSalaryfromEmployeeorderbySalarydes...
Write a SQL query to get the second highest salary from theEmployeetable. +---+---+ | Id | Salary | +---+---+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +---+---+ For example, given the above Employee table, the second highest salary is200. If there is no second high...
SQL代码: select IFNULL((select distinct(Salary) from Employee order by Salary desc limit 1,1),null) as SecondHighestSalary 学到了一个sql函数IFNULL,该函数的作用是判断查询结果集是否为空,若为空则返回第二个参数 还有一种做法,直接用两个select解决 ...
SQL代码: selectIFNULL((selectdistinct(Salary) fromEmployee orderbySalarydesc limit1,1),null)asSecondHighestSalary 学到了一个sql函数IFNULL,该函数的作用是判断查询结果集是否为空,若为空则返回第二个参数 还有一种做法,直接用两个select解决 select(selectdistinct(Salary)fromEmployee ...
In this article, I am going to explain to you how to find the second highest salary in various ways. This is one of the most common questions asked in an SQL interview. Below are the several ways of finding the Nth highest salary: How to find the second highest salary in SQL Server ...
7. For example, given the above Employee table, the second highest salary is200. If there is no second highest salary, then the query should returnnull. 解决方案如下: # Write your MySQL query statement below select max(Salary) from Employee where Salary<(select max(Salary) from Employee )...
三、SQL 语句 3.1 自己写的 SELECT a.Salary AS SecondHighestSalary FROM Employee AS a JOIN Employee AS b ON a.Salary < b.Salary GROUP BY a.Id HAVING COUNT(a.Id) = 1; 注意:这种方法是错误的,因为当没有薪水第二高的人的时候,返回的是空值,不是 null。 3.2 修改版 将之前写的变成子查询,就...