Ранг текущих зарплат всех сотрудников по полю salary в порядке от 1 до N

Описание задачи

Необходимо ранжировать текущие зарплаты всех сотрудников по полю salary в порядке от 1 до N. При равной зарплате ранги должны совпадать, а при одинаковых рангах сортировка должна выполняться по полю emp_no в порядке возрастания.

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`)
);

Ответ

SELECT t1.emp_no, t1.salary, COUNT(DISTINCT t2.salary) AS rank FROM (SELECT * FROM salaries WHERE to_date = '9999-01-01') t1, (SELECT * FROM salaries WHERE to_date = '9999-01-01') t2 WHERE t2.salary >= t1.salary GROUP BY t1.emp_no ORDER BY rank

Объяснение решения

Данная задача не проходит проверку на MySQL, но успешно проходит тестирование на SQLite (например, на платформе Nowcoder).

1. Создаем два временных представления t1 и t2:

(SELECT * FROM salaries WHERE to_date = '9999-01-01') t1
(SELECT * FROM salaries WHERE to_date = '9999-01-01') t2

2. Сравниваем значения поля salary между представлениями t1 и t2. Это позволяет получить набор данных, где зарплата сотрудника из t1 меньше зарплаты сотрудника из t2. На основе этого можно группировать данные и считать количество уникальных значений зарплат, превышающих текущую зарплату. Это значение будет использоваться как ранг. Почему используется DISTINCT? Потому что могут быть сотрудники с одинаковым рангом.

SELECT t1.emp_no, COUNT(DISTINCT t2.salary) FROM t1, t2 WHERE t2.salary > t1.salary GROUP BY t1.emp_no