📝 문제 설명
Table Schema: 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 second highest distinct salary from the Employee table. If there is no second highest salary, return null (return None in Pandas).
The result format is in the following example.
📖 예시
Input:
Employee table:
| id | salary |
|---|---|
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
Output:
| id | salary |
|---|---|
| 1 | 100 |
Example 2:
Input:
Employee table:
| SecondHighestSalary |
|---|
| 200 |
Output:
| SecondHighestSalary |
|---|
| null |
💻 코드
내코드
select salary as SecondHighestSalary
from employee
having salary < max(salary)
order by salary desc
limit 1정답코드
SELECT MAX(a.salary) AS SecondHighestSalary
FROM Employee a
JOIN Employee b
ON a.salary < b.salary;
🔍 분석
- ON a.salary < b.salary
-> 내 연봉보다 더 큰 연봉이 있는 경우에만 연결(1등은 자기보다 더 큰 사람이 없으니 연결 안됨)
-MAX(a.salary)
-> 남은 사람들 중에서 가장 큰 값 = 2등
