문제
Table: Employee
+-------------+------+
| Column Name | Type |
+-------------+------+
| id | int |
| salary | int |
+-------------+------+
id is the primary key column for this table.
Each row of this table contains information about the salary of an employee.
Plain Text
복사
Write an SQL query to report the second highest salary from the Employee table. If there is no second highest salary, the query should report null.
예시
예시1
Input:
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
Output:
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
Plain Text
복사
예시2
Input:
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1 | 100 |
+----+--------+
Output:
+---------------------+
| SecondHighestSalary |
+---------------------+
| null |
+---------------------+
Plain Text
복사
정답
SELECT MAX(Salary) SecondHighestSalary
FROM Employee
WHERE Salary < (SELECT MAX(salary) FROM Employee);
SQL
복사
풀이
•
우선 Salary가 현재 테이블에서 낮은 사람들만을 조회하고 그 데이터들의 MAX 값을 조회하면 된다.
•
MAX는 NULL이 들어오면 NULL을 출력한다.