SQL实战练习题(1)-求第二高的薪资-leetcode sql 176

问题

答案

-- 方法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 < e2.Salary 
);