题目:
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 query should return 200
as the second highest salary. If there is no second highest salary, then the query should return null
.
+---------------------+ | SecondHighestSalary | +---------------------+ | 200 | +---------------------+分析:
# Write your MySQL query statement below
#给定工资表,查找工资第2的数据,如果不存在该数据,则返回null
#先找出k个大的数据,之后在该批数据中查找最小的
#防止只有一个数据的情况
select max(emp.salary) as 'SecondHighestSalary'
from
(select salary
from employee
group by salary
order by salary desc
limit 1,1) as emp;
#科教篇 limit来表示偏移量,可以直接取第2大的数据
# SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset;
#limit start,OFFSET offset
# 入门篇
# SELECT * FROM table LIMIT 0,10;//检索记录行1-10
# 进阶篇
# SELECT * FROM table LIMIT 2,10;//检索记录行3-13
# SELECT * FROM table LIMIT 5,20;//检索记录行6-25
# 高级篇
# SELECT* FROMtable LIMIT 5,-1;//检索记录行6到结尾数据
# SELECT* FROMtable LIMIT 0,-1;//检索全部记录
# SELECT * FROM table LIMIT 5; //检索前 5 个记录行