创建函数 与 调用 函数

 

 

 

第N高的薪水 - LeetCode (中国) https://leetcode-cn.com/problems/nth-highest-salary/description/

 

 

 

CREATE FUNCTION getNthHighestSalary (N INT) RETURNS INT
BEGIN

DECLARE dynamic_f VARCHAR (255);


SET dynamic_f = CONCAT(
	'getNthHighestSalary(',
	N,
	')'
);


SET N = N - 1;

RETURN (
	SELECT
		CASE (
			SELECT
				COUNT(DISTINCT(Salary)) > N
			FROM
				Employee
		)
	WHEN 0 THEN
		NULL
	ELSE
		(
			SELECT DISTINCT
				(Salary)
			FROM
				Employee
			ORDER BY
				Salary DESC
			LIMIT N,
			1
		)
	END AS dynamic_f
);


END

　　

 

Write a SQL query to get the nth highest salary from the Employee table.

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

For example, given the above Employee table, the nth highest salary where n = 2 is 200. If there is no nth highest salary, then the query should return null.

+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+