176. Second Highest Salary
ifnull函数,ifnull(n1, n2) — 如果n1≠null,返回n1, 如果n1=null,返回null
select ifnull(
(select distinct salary
from employee
order by salary desc
limit 1 offset 1), NULL) as SecondHighestSalary
177. Nth Highest Salary
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
declare m int;
set m=n-1;
RETURN (
select ifnull(
(select distinct salary
from employee
order by salary desc
limit 1 offset m), NULL) as getNthHighestSalary
);
END
- 如果直接用n-1会报错
要设m=n-1 声明用declare - 需要distinct() 才有null
178. Rank Scores
select score as Score, (select count(distinct score) from scores where score >= s.score)