SQL实战练习题(1)求第二高的薪资leetcodesql176

问题

答案

-- 方法1: where子查询+max
SELECT max(salary)
from leetcode_db.employee AS ta
where salary <(select MAX(Salary) from leetcode_db.employee AS tb);


-- 方法2：ifnull+group by + limit
SELECT
IFNULL(
(SELECT Salary
FROM employee e
group by Salary
order by Salary DESC
limit 1,1), null) AS c;


-- 方法3：缺点: 查询结果可能是空集(不是null)，替换max(salary)即可
SELECT *
FROM employee e
group by Salary
order by Salary DESC
limit 1,1;


-- 方式4：排名第2，也就是只有一个数大于它(N-1之下，万人之上)
SELECT DISTINCT Salary -- 去掉distinct，则返回并列排名的多个值，但有可能是空集，可以换成max(salary)
FROM employee e
WHERE 1=(
SELECT count(DISTINCT Salary)
FROM employee e2
WHERE e.Salary );