LeetCode 코딩 테스트 - Nth Highest Salary(LV.Medium)

 

 

Table: Employee

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+
id is the primary key (column with unique values) for this table.
Each row of this table contains information about the salary of an employee.

 

Write a solution to find the nth highest salary from the Employee table. If there is no nth highest salary, return null.

The result format is in the following example.

 

Example 1:

Input: 
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
n = 2
Output: 
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+

Example 2:

Input: 
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
+----+--------+
n = 2
Output: 
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| null                   |
+------------------------+

 

# 쿼리를 작성하는 목표, 확인할 지표 : n번째 높은 연봉을 출력하는 함수 / salary
# 쿼리 계산 방법 : 1. 연봉 내림차순 -> 2. n - 1번째 행을 건너뛰고 다음 행 추출
# 데이터의 기간 : x
# 사용할 테이블 : employee
# JOIN KEY : x
# 데이터 특징 : x
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
# 2 LIMIT과 OFFSET은 산술 연산을 읽지 못한다. 정수를 넣어야 한다.
SET N = N - 1;

  RETURN (
    SELECT
        DISTINCT salary
    FROM employee
    # 1
    ORDER BY
        salary DESC
    LIMIT 1
    # 2
    OFFSET N
  );

END