SQL/MySQL 문제풀이

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

혜린티나 2022. 6. 28. 08:59

[문제]

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

 

Nth Highest Salary - LeetCode

Level up your coding skills and quickly land a job. This is the best place to expand your knowledge and get prepared for your next interview.

leetcode.com

 

[풀이]

- 서브쿼리 풀이 

 

   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