工资表取第二高薪资、取N高薪资
表
CREATE TABLE `employee` (
`Id` int(20) DEFAULT NULL,
`Salary` decimal(8,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
数据
insert into employee values(1,1800),(2,17800),(3,2750),(4,7800),(4,7800),(5,7400),(6,6400),(7,6584),(8,3854)
取第二高薪资
select Salary from employee order by Salary desc limit 1,1 //默认第一条下标0
取N高薪资
CREATE FUNCTION getHightGrade(N INT) RETURNS INT
BEGIN
set N=N-1;
RETURN(select IFNULL((select Salary from employee ORDER BY Salary desc limit N,1),null) as hightGrade
);
END