目录
题目
表: Employee
+-------------+------+
| Column Name | Type |
+-------------+------+
| id | int |
| salary | int |
+-------------+------+
Id是该表的主键列。
该表的每一行都包含有关员工工资的信息。
编写一个SQL查询来报告 Employee 表中第 n 高的工资。如果没有第 n 个最高工资,查询应该报告为 null 。
查询结果格式如下所示。
示例 1:
输入:
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
n = 2
输出:
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200 |
+------------------------+
示例 2:
输入:
Employee 表:
+----+--------+
| id | salary |
+----+--------+
| 1 | 100 |
+----+--------+
n = 2
输出:
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| null |
+------------------------+
解法
SELECT distinct salary from Employee order by salary DESC limit N,1
SELECT if(count(salary)<N,NULL,MIN(a.salary)) as getNthHighestSalary from (SELECT salary from Employee group by salary order BY salary desc limit N) AS a
提问
第一个sql是否比第二个sql快
分析
假设情况如示例1,按照sql的执行顺序:
第一个sql执行顺序为from>select>distinct>order by>limit
distinct全表查,order by全表差,limit全表查
一共查询3+3+3=9次
第二个sql执行顺序为from> group by> select>order by>from>if>count>min
group by全表查,order by全表查,count子循环中查,min子循环中查
一共查询3+3+n+n次
当3+3+n+n<9,n=1时,第二个sql快;
当3+3+n+n>9,n>1时,第一个sql快;