Определить, хватает ли подарков на складе для всех доставок, находящихся в статусе "сборка"

Условие:

Хватит ли подарков на складе для всех доставок? Напишите запрос, который покажет разницу между доступным количеством подарков и требуемым для доставок в статусе "сборка". Сортируйте от меньшего к большему.

Условие: Хватит ли подарков на складе для всех доставок? 
Напишите запрос, который покажет разницу между доступным количеством подарков и 
требуемым для доставок в статусе

Решение:

Предмет: Базы данных
Раздел: SQL-запросы, агрегация, соединения (JOIN), фильтрация данных


? Задача:

Нужно определить, хватает ли подарков на складе для всех доставок, находящихся в статусе "сборка".
Для этого необходимо:

  1. Посчитать требуемое количество подарков по каждому складу и типу подарка.
  2. Посчитать доступное количество подарков на складе.
  3. Вычислить разницу между доступным и требуемым количеством.
  4. Отсортировать результат от меньшего к большему (то есть сначала покажем те, где нехватка или на грани).

? Объяснение структуры:

  • Таблица deliveries содержит статус доставки и warehouse_id, где находится подарок.
  • Таблица letters указывает, какой подарок (gift_id) требуется.
  • Таблица stocks показывает, сколько подарков есть на складе (warehouse_id, gift_id, quantity).

✅ SQL-запрос:

SELECT 
    s.warehouse_id,
    s.gift_id,
    COALESCE(s.quantity, 0) AS available_quantity,
    COALESCE(r.required_quantity, 0) AS required_quantity,
    COALESCE(s.quantity, 0) - COALESCE(r.required_quantity, 0) AS difference
FROM stocks s
LEFT JOIN (
    SELECT 
        d.warehouse_id,
        l.gift_id,
        COUNT(*) AS required_quantity
    FROM deliveries d
    JOIN letters l ON d.letter_id = l.letter_id
    WHERE d.delivery_status = 'сборка'
    GROUP BY d.warehouse_id, l.gift_id
) r ON s.warehouse_id = r.warehouse_id AND s.gift_id = r.gift_id
ORDER BY difference ASC;

? Пояснение:

  • В подзапросе r мы считаем, сколько подарков требуется на каждом складе, фильтруя по статусу сборка.
  • Основной запрос соединяет stocks с этим подзапросом по складу и подарку.
  • Используем COALESCE, чтобы заменить NULL на 0 (если нет записей в одной из таблиц).
  • Вычисляем разницу между доступным и требуемым количеством.
  • Сортируем по этой разнице, чтобы в начале были те, где нехватка.

? Пример результата:

warehouse_idgift_idavailable_quantityrequired_quantitydifference
2101510-5
1102880
310315105

Если в таблице stocks отсутствует комбинация warehouse_id и gift_id, которая есть в deliveries, то такие случаи можно также включить, используя FULL OUTER JOIN (если поддерживается), либо объединить с подзапросом через UNION.

Нужна версия с учетом этого?

Не нашли нужного вам решения? Оставьте заявку и наши авторы быстро и качественно помогут вам с решением.
Оставить заявку
Работа вам нужна срочно. Не волнуйтесь, уложимся!
  • 22423 авторов готовы помочь тебе.
  • 2402 онлайн