176. Second Highest Salary

r囧r小猫 2022-02-13 19:36 256阅读 0赞 
  1. Write a SQL query to get the second highest salary from the Employee table.
  3. +----+--------+
  4. | Id | Salary |
  5. +----+--------+
  6. | 1  | 100    |
  7. | 2  | 200    |
  8. | 3  | 300    |
  9. +----+--------+
  10. For example, given the above Employee table, the query should return 200 as the second highest salary. If there is no second highest salary, then the query should return null.
  12. +---------------------+
  13. | SecondHighestSalary |
  14. +---------------------+
  15. | 200                 |
  16. +---------------------+

本题主要考察SQL中limit和offset的用法。以下列出比较简洁的两种解法：

  1. Select
  2.     (Select Distinct Salary
  3.     From Employee
  4.     Order By Salary Desc
  5.     Limit 1 Offset 1) AS SecondHighestSalary
  7. SELECT 
  8.   CASE 
  9.   WHEN COUNT(DISTINCT(Salary)) <= 1 THEN null 
  10.   ELSE  (select salary from employee order by salary desc limit 1, 1)
  11.   END 
  12. AS SecondHighestSalary 
  13. FROM Employee

两种解法刚好涵盖了limit的两种用法，两种用法区别如下：

① select * from table limit 2 offset 1;

//含义是从第1条（不包括）数据开始取出2条数据，limit后面跟的是2条数据，offset后面是从第1条开始读取，即读取第2,3条

② select * from table limit 2,1;

//含义是跳过2条取出1条数据，limit后面是从第2条开始读，读取1条信息，即读取第3条数据

发表评论

表情：
评论列表 （有 0 条评论，256人围观）

还没有评论，来说两句吧...

相关阅读