SQL自定义函数的应用

编写一个SQL语句，查询表’employee’中第N高的薪水。

Id	Salary
1	8900
2	9200
3	11150
4	11050
5	9700

如上表所示，如果n=3,则应返回’9700’，如果不存在第三高的薪水则返回NULL。

getNthHighestSalary(3)
9700

代码实现：

#第一种方法
#创建函数
CREATE FUNCTION getNthHighestSalary(@N INT) RETURNS INT as
BEGIN
    RETURN (
        select distinct Salary from
        (
            select Salary,dense_rank() over(order by Salary desc) rnk from Employee
        ) t where rnk=@N    
    );
END
#查看函数应用结果
select dbo.getNthHighestSalary('3') as result




#第二种方法
#创建函数
CREATE FUNCTION getNthHighestSalary2(@N INT) RETURNS INT AS
BEGIN
	RETURN (
	select ISNULL(Salary,null) from
	(select ROW_NUMBER() over(order by tmp.Salary desc) as nums, tmp.Salary from (select distinct salary from Employee) as tmp)
	as tmpSalary where tmpSalary.nums= @N )
	END
#查看函数应用结果
select dbo.getNthHighestSalary2('3') as result
select dbo.getNthHighestSalary2('10') as result