# 共800个题目: leetcode177 : 获取 Employee
表中第 n 高的薪水(Salary)
https://leetcode-cn.com/problems/nth-highest-salary/
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
例如上述 Employee 表,n = 2 时,应返回第二高的薪水 200。如果不存在第 n 高的薪水,那么查询应返回 null。
解答:
1.自己解决该问题可依据 返回第二高的薪水,SELECT (SELECT DISTINCT Salary S from Employee E DESC LIMIT 1 OFFSET 1) AS HIGH_N;
知识点:
A1: limit 1,2 区别于:limit 1 offset 1;
A2: select子查询会输出,某个字段为null
扩展知识:输出第N 高的薪水。https://leetcode-cn.com/problems/nth-highest-salary/comments/
评论解法1:【逻辑复杂,不易读,注意重新理解易读易维护的代码】if(a,null)如果a有结果输出a,否则输出null;
a是 输出第N高,是先查询出前N高的数据,在取出最小的data,
select (IF(
(select count(*) from (select distinct e.Salary from Employee e) e ) >=N,
(select min(e.Salary) from (select distinct e.Salary from Employee e order by e.Salary desc limit N) e),
NULL))
**知识点:**
函数if 和 count(),min()函数,limit N降序取前N个之后,再取N中最小的哪一个,
不太懂,IF(A,NULL)用法 ; if(A,TRUE,FLASE) A为真且不为null,输出TRUE的表达式,否则返回 FALSE.
IF函数正确使用方法:1 可以作为条件语句等于 case when语句,2.作为控制语句;
条件语句:IF(expr1,expr2,expr3)
1 如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL),则 IF()的返回值为expr2; 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值,具体情况视其所在语境而定。
select *,if(sva=1,"男","女") as Sva from taname where sva != ""
原文:https://blog.csdn.net/wzzfeitian/article/details/55097563
case when 用法1:
select CASE sva WHEN 1 THEN '男'
ELSE '女' END
as ssva from taname where sva != ''
case when 用法2:
SELECT CASE 1 WHEN 1 THEN 'one'
WHEN 2 THEN 'two'
ELSE 'more' END as testCol;
评论解法2:
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
select distinct Salary from Employee e where N = (select count(distinct Salary) from Employee where Salary >= e.Salary )
);
END
认识到SQL函数语句的复杂性。CREATE函数,func用来返回第N高薪水的函数,getNthHighestSalary(N INT)
select distinct Salary from Employee e where N = (select count(distinct Salary) from Employee where Salary >= e.Salary )分析:思路是,N = 表中数据满足 条件where Salary >= e.Salary 的个数count(distinct Salary)是2.
重点:理解 count(*)函数用法,
解法3:
set用法; limit N,1用法解释:这是两个参数,第一个是偏移量,第二个是数目 ; 如果是一个参数n;即为limit n返回前n行,区别于:limit 2 OFFSET 4; 这是前4行跳过,从第5行开始返回2行,即第5,6行。
该题目中 SET N = N-1,结合 desc limit N,1;刚好返回的就是第N个值。即偏移N-1个值,且返回1行为第N行。
CREATE FUNCTION geNthhHighestSalary(N INT) RETURNS INT
BEGIN
SET N = N - 1;
RETURN (
SELECT DISTINCT salary FROM employee ORDER BY salary DESC LIMIT N, 1
);
END
解法 4 :对不存在第N高,NULL做处理,用IFNULL函数。
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set N=N-1;
RETURN (
# Write your MySQL query statement below.
select ifnull
(
(
select distinct Salary
from Employee
order by Salary desc
limit N,1
),null
)as 'GetNHighestSalary'
);
END
190411:面试SQL