Input:
Employee table:
+----+-------+--------+--------------+
| id | name | salary | departmentId |
+----+-------+--------+--------------+
| 1 | Joe | 85000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
| 7 | Will | 70000 | 1 |
+----+-------+--------+--------------+
Department table:
+----+-------+
| id | name |
+----+-------+
| 1 | IT |
| 2 | Sales |
+----+-------+
Output:
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Joe | 85000 |
| IT | Randy | 85000 |
| IT | Will | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+
Explanation:
In the IT department:
- Max earns the highest unique salary
- Both Randy and Joe earn the second-highest unique salary
- Will earns the third-highest unique salary
In the Sales department:
- Henry earns the highest salary
- Sam earns the second-highest salary
- There is no third-highest salary as there are only two employees
Plain Text
볡μ¬
νμ¬μ μμλ€μ νμ¬μ κ° λΆμμμ λκ° κ°μ₯ λ§μ λμ λ²λμ§ νμΈνλ λ° κ΄μ¬μ΄ μλ€.
ν λΆμμ κ³ μλμλ ν΄λΉ λΆμμ μμ 3κ°μ κ³ μ κΈμ¬λ₯Ό λ°λ μ§μμ
λλ€.
κ° λΆμμμ κ³ μλμμΈ μ§μμ μ°ΎκΈ° μν΄ SQL 쿼리λ₯Ό μμ±ν©λλ€.
κ²°κ³Ό ν
μ΄λΈμ μμμ μμλ‘ λ°νν©λλ€.
쿼리 κ²°κ³Ό νμμ λ€μ μμ μ κ°μ΅λλ€.
β’
RANK(), DENSE_RANK()
SELECT
t.department
, t.employee
, t.salary
FROM (
SELECT department.name AS department
, employee.name AS employee
, employee.salary AS salary
, DENSE_RANK() OVER (PARTITION BY departmentid ORDER BY salary DESC) AS dr
FROM employee
JOIN department ON employee.departmentid = department.id
) AS T
WHERE t.dr <= 3
;
SQL
볡μ¬