Второй по величине элемент

Решение

Задача требует найти вторую по величине зарплату из таблицы Employees. Это классическая SQL задача с несколькими подходами разной сложности и эффективности.

Решение 1: DISTINCT + ORDER BY + LIMIT (Рекомендуемое)

Простое и понятное решение, которое работает в подавляющем большинстве случаев.

SELECT MAX(salary) AS second_largest
FROM Employees
WHERE salary < (SELECT MAX(salary) FROM Employees);

Решение 2: DISTINCT с OFFSET (Альтернативное)

Использует DISTINCT для удаления дубликатов зарплат, затем сортирует и берёт вторую.

SELECT DISTINCT salary
FROM Employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

Решение 3: Подзапрос с MAX (Оптимальное)

Находит максимальную зарплату среди всех, которые меньше чем глобальный максимум.

SELECT MAX(salary) AS second_largest_salary
FROM Employees
WHERE salary < (SELECT MAX(salary) FROM Employees);

Решение 4: Window Functions (Модерный подход)

Использует оконные функции — доступно в PostgreSQL, MySQL 8.0+, SQL Server.

SELECT DISTINCT salary
FROM (
    SELECT 
        salary,
        RANK() OVER (ORDER BY salary DESC) AS rank
    FROM Employees
) ranked
WHERE rank = 2;

Решение 5: Более универсальное решение с обработкой NULL

Обрабатывает случаи, когда второй по величине зарплаты может быть NULL.

SELECT COALESCE(
    (SELECT DISTINCT salary
     FROM Employees
     ORDER BY salary DESC
     LIMIT 1 OFFSET 1),
    NULL
) AS second_largest_salary;

Примеры использования

-- Создание тестовой таблицы
CREATE TABLE Employees (
    id INT PRIMARY KEY,
    salary INT
);

-- Вставка тестовых данных
INSERT INTO Employees VALUES
(1, 100),
(2, 200),
(3, 150);

-- Проверка решений
-- Решение 1 и 3 (эквивалентны)
SELECT MAX(salary) AS second_largest
FROM Employees
WHERE salary < (SELECT MAX(salary) FROM Employees);
-- Результат: 150

-- Решение 2
SELECT DISTINCT salary
FROM Employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
-- Результат: 150

Сравнение подходов

Граничные случаи и их решение

Случай 1: Таблица с дубликатами зарплат

-- Данные
INSERT INTO Employees VALUES
(1, 100),
(2, 200),
(3, 200),  -- Дубликат
(4, 150);

-- Решение (вторая по величине УНИКАЛЬНАЯ)
SELECT MAX(salary) AS second_largest
FROM Employees
WHERE salary < (SELECT MAX(salary) FROM Employees);
-- Результат: 150 (корректно)

Случай 2: Таблица с менее чем двумя уникальными зарплатами

-- Данные
INSERT INTO Employees VALUES
(1, 100),
(2, 100);

-- Решение вернёт NULL
SELECT MAX(salary) AS second_largest
FROM Employees
WHERE salary < (SELECT MAX(salary) FROM Employees);
-- Результат: NULL

Случай 3: Использование COALESCE для обработки NULL

SELECT COALESCE(
    (SELECT MAX(salary)
     FROM Employees
     WHERE salary < (SELECT MAX(salary) FROM Employees)),
    0  -- Или другое значение по умолчанию
) AS second_largest_salary;

Window Functions (PostgreSQL/MySQL 8.0+)

WITH ranked_salaries AS (
    SELECT 
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
    FROM Employees
)
SELECT salary
FROM ranked_salaries
WHERE salary_rank = 2;

Разница между RANK() и DENSE_RANK():

• RANK(): Пропускает номера при дубликатах (1, 1, 3...)
• DENSE_RANK(): Не пропускает номера (1, 1, 2...)

Для этой задачи лучше использовать DENSE_RANK(), чтобы корректно получить вторую по величине зарплату, игнорируя дубликаты.

Рекомендация для QA

Используйте Решение 3 (MAX с подзапросом):

• Просто и понятно
• Работает во всех СУБД
• Правильно обрабатывает дубликаты
• Легко тестировать с разными граничными случаями