Блог

У меня есть таблица для хранения наименования товара, количества и дня поступления товара на складе. В неблагоприятных условиях счета-фактуры на вышеуказанные товары будут отправлены позже. Я укажу название продукта и количество в счете-фактуре в таблице B. Проблема здесь в том, что я хочу проверить количество товаров с выставлением счета и без выставления счета. Обновление товаров с помощью счетов-фактур будет следовать FIFO.

Пример:

Таблица А

Таблица В

Я решил эту проблему, создав новую таблицу C с теми же данными, что и в таблице A.

Таблица C

Затем я получаю данные в таблице B, сгруппированные по good_id, и сохраняю их в $data. Использование php для получения $данных и проверки состояния:

Я обновил ПОРЯДОК таблиц C created_at ПО ограничению 1 следующим образом:

• если ( tableC . current_num — $data['num'] < 0), то обновление current_number = 0, invoice_number = $data['num'] — tableC .. current_num Значение обновления $data['num'] = $data['num'] — tableC . current_num
• если ( tableC . current_num — $data['num'] > 0) или (> tableC . current_num — $data[‘num’] = 0), то обновление current_number = tableC . current_num — $data['num'] , invoice_number = $data['num'] .

таблица C после обновления

Я решил проблему php вот так. Однако при наборе данных около 100 000 строк я думаю, что внутренняя обработка займет много времени. Может ли кто-нибудь подсказать мне более разумный способ справиться с этим?

Обновленное решение для MySQL 5.7:

Тестовый набор для поддержки MySQL 5.7 , PG, MariaDB до 10.2.2 и т. Д:

Тестовый пример для MySQL 5.7 и т. Д

Для MySQL 5.7: Это заменяет функцию окна (для выполнения SUM) и использует производные таблицы вместо WITH clause .

 SELECT id, good_id
     , num - GREATEST(num - GREATEST(balance, 0), 0) AS num
     , created_at
     , GREATEST(num - GREATEST(balance, 0), 0) AS invoice_num
  FROM (
         SELECT MIN(t2.id) AS id, MIN(t2.num) AS num
              , t2.good_id, t2.created_at
              , MIN(o.num_invoice) AS num_invoice
              , SUM(t1.num) - MIN(o.num_invoice) AS balance
           FROM tableA AS t1
           JOIN tableA AS t2
             ON t1.good_id = t2.good_id
            AND t1.created_at <= t2.created_at
           JOIN (
                   SELECT good_id, SUM(num_invoice) AS num_invoice
                     FROM tableB
                    GROUP BY good_id
                ) AS o
             ON o.good_id = t1.good_id
          GROUP BY t2.good_id, t2.created_at
       ) AS cte2
 ORDER BY created_at
;
 

Для баз данных, которые должным образом обрабатывают функциональную зависимость GROUP BY , мы могли бы просто GROUP BY t2.id ( primary key из tableA ) и удалить MIN(t2.id) и MIN(t2.num) то и другое .

Подобный этому:

Тестовый случай

 -- For MySQL 5.7
SELECT id, good_id
     , num - GREATEST(num - GREATEST(balance, 0), 0) AS num
     , created_at
     , GREATEST(num - GREATEST(balance, 0), 0) AS invoice_num
  FROM (
         SELECT t2.id, t2.num
              , t2.good_id, t2.created_at
              , MIN(o.num_invoice) AS num_invoice
              , SUM(t1.num) - MIN(o.num_invoice) AS balance
           FROM tableA AS t1
           JOIN tableA AS t2
             ON t1.good_id = t2.good_id
            AND t1.created_at <= t2.created_at
           JOIN (
                   SELECT good_id, SUM(num_invoice) AS num_invoice
                     FROM tableB
                    GROUP BY good_id
                ) AS o
             ON o.good_id = t1.good_id
          GROUP BY t2.id
       ) AS cte2
 ORDER BY created_at
;
 

Оригинальный ответ с использованием оконных функций и WITH clause :

Вот тестовый пример с PG 13, но он отлично работает с MySQL 8 или MariaDB 10.2.2 .

Примечание: Я оставил это как просто запрос, который генерирует запрошенную информацию. Не ясно, нужна ли 3-я таблица. Это может быть использовано для обновления (или создания) этой таблицы, если это необходимо.

Тестовый случай:

Рабочий тестовый случай

Условия CTE:

1. cte1 — рассчитайте общее количество запрошенных товаров
2. cte2 — Рассчитайте текущий баланс на основе текущих запасов по дате

Наконец, мы используем cte2 для определения выделенных и оставшихся товаров, запрошенных вопросом.

 WITH cte1 (good_id, num_invoice) AS (
        SELECT good_id, SUM(num_invoice) AS num_invoice
          FROM tableB
         GROUP BY good_id
     )
   , cte2 AS (
       SELECT a.*, o.num_invoice
            , SUM(num) OVER (PARTITION BY a.good_id ORDER BY created_at) - o.num_invoice AS balance
         FROM tableA AS a
         JOIN cte1   AS o
           ON o.good_id = a.good_id
     )
SELECT id, good_id
     , num - GREATEST(num - GREATEST(balance, 0), 0) AS num
     , created_at
     , GREATEST(num - GREATEST(balance, 0), 0) AS invoice_num
  FROM cte2
 ORDER BY created_at
;
 

Результат:

  ---- --------- ------ ------------ ------------- 
| id | good_id | num  | created_at | invoice_num |
 ---- --------- ------ ------------ ------------- 
|  1 |       1 |    0 | 2021-09-24 |          10 |
|  2 |       1 |    3 | 2021-09-25 |           2 |
|  3 |       1 |    7 | 2021-09-26 |           0 |
 ---- --------- ------ ------------ ------------- 
 

Примечание: Я добавил одну дополнительную запись от руки для 7 товаров (id = 3), чтобы проверить крайний случай.

Настройка тестового случая:

 CREATE TABLE tableA (
   id           int primary key
 , good_id      int
 , num          int
 , created_at   date
);

CREATE TABLE tableB (
   id           int primary key
 , good_id      int
 , num_invoice  int
);

INSERT INTO tableA VALUES
  (1,1,10,'2021-09-24')
, (2,1, 5,'2021-09-25')
, (3,1, 7,'2021-09-26')
;

INSERT INTO tableB VALUES
  (1,1,12)
;