[LeetCode] Nth highest salary - 사용자 정의 함수 풀이

[문제]

https://leetcode.com/problems/nth-highest-salary/

Nth Highest Salary - LeetCode

 

[풀이]

- 서브쿼리 풀이 

 

   case 문 

CREATE FUNCTION getNthHighestSalary(N INT) 
RETURNS INT 
BEGIN 
 RETURN (
      SELECT CASE WHEN COUNT(sub.salary) < N THEN NULL 
                  ELSE MIN(sub.salary) 
              END 
      FROM(
      SELECT DISTINCT salary
      FROM employee
      ORDER BY salary DESC 
      LIMIT N ) sub
 );
END

 

   if 함수 

 

CREATE FUNCTION getNthHighestSalary(N INT) 
RETURNS INT 
BEGIN 
 RETURN (
      SELECT IF( COUNT(sub.salary) < N , NULL , MIN(sub.salary))
      FROM(
      SELECT DISTINCT salary
      FROM employee
      ORDER BY salary DESC 
      LIMIT N ) sub
 );
END

 

- LIMIT OFFSET 풀이

 

CREATE FUNCTION getNthHighestSalary(N INT) 
RETURNS INT 
BEGIN 
     SET N = N-1;
 RETURN ( 
     SELECT DISTINCT salary 
     FROM employee
     ORDER BY salary DESC 
     LIMIT 1 OFFSET N
 );
END