Блог

Я создаю систему отслеживания проката оборудования, которая управляет запасом, выделенным для каждого задания, этот запас также распределяется по ящикам.

У меня есть следующие таблицы:

Поля таблицы (список контейнеров, назначаются заданию на этапе выполнения, задание устанавливается пустым при возврате)

 box
job
...
  

Коды таблиц (типы запасов)

 code
dept
type
description
...
  

Табличные задания (jobs)

 job
client
name
...
  

Заказы таблиц (заказы, связанные с заданием)

 id
job
...
  

Элементы порядка таблицы (элементы, связанные с заказом)

 id
order
code
quantity
...
  

Таблица выполнена (выполнение элемента заказа, заявка количество при отправке, — количество при возврате)

 id
orderitem
code
quantity
...
  

Запас таблицы ( количество при покупке, — количество при утилизации)

 id
code
quantity
...
  

Таблица stockbox (применить или — количество кода к ячейке)

 id
code
box
quantity
...
  

Я сгенерировал результирующий набор, который показывает все результаты выполнения задания, когда СУММА (количество) > 0 (чтобы игнорировать возвращенные элементы):

 SELECT
    '' AS box,
    codes.code AS item,
    '' AS parent,
    codes.code AS code,
    codes.dept AS codedept,
    codes.type AS codestype,
    codes.description AS codedesc,
    '' AS make,
    '' AS model,
    '' AS name,
    '' AS serial,
    '' AS status,
    '' AS LOCATION,
    orders.job AS job,
    SUM(fulfils.quantity) AS quantity
FROM
    fulfils
    LEFT JOIN orderitems ON orderitems.id = fulfils.orderitem
    LEFT JOIN orders ON orders.id = orderitems.order
    LEFT JOIN codes ON codes.code = fulfils.code
WHERE
    orders.job = 'TEST'
GROUP BY
    fulfils.code,
    codes.dept,
    codes.type,
    codes.description,
    orders.job
HAVING
    quantity > 0;
  

This works as expected.

My challenge is now to separate this out into boxes based on their allocation to the job and their total stockbox allocation (SUM of stockbox quantities for that box and code). Also I need to include the fulfil total not allocated to any box (SUM of quantity — SUM of quantity assigned to a box). I’m ignoring the stock table for this purpose as not relevant, only stockbox.

Предполагая, что поле ‘BOX01’ назначено заданию ‘TEST’, ‘BOX01’ имеет СУММУ 3. Задание ‘TEST’, код ‘PRODUCT01’ через table fulfills имеет СУММУ 5, я хочу увидеть эти результаты при выполнении запроса для .job = ‘TEST’.

  ------- ----------- ----- ---------- 
|  box  |   item    | ... | quantity |
 ------- ----------- ----- ---------- 
| BOX01 | PRODUCT01 | ... |        3 |
|       | PRODUCT01 | ... |        2 |
 ------- ----------- ----- ---------- 
  

Я полностью открыт для изменений дизайна. Простым обходным решением было бы добавить ‘box’ к fulfuls , но я хотел бы иметь возможность перераспределять запасы между блоками, как только запасы будут выполнены, не влияя на таблицу выполнения.

Заранее большое спасибо.

Обновить:

Это подводит меня к чему-то близкому, создавая строки для каждого блока, который выделен для задания и запаса в нем. Теперь мне нужно вычесть СУММУ значений в штучной упаковке из оставшегося количества выполнения.

 SELECT
    stockbox.box AS box,
    codes.code AS item,
    stockbox.box AS parent,
    codes.code AS code,
    codes.dept AS codedept,
    codes.type AS codestype,
    codes.description AS codedesc,
    '' AS make,
    '' AS model,
    '' AS name,
    '' AS serial,
    '' AS status,
    '' AS location,
    boxes.job AS job,
    SUM(stockbox.quantity) AS quantity
FROM
    stockbox
    LEFT JOIN codes ON codes.code = stockbox.code
    LEFT JOIN boxes ON boxes.box = stockbox.box
WHERE
    boxes.job = 'TEST'
GROUP BY
    stockbox.box,
    codes.code,
    codes.dept,
    codes.type,
    codes.description,
    boxes.box
HAVING
    quantity > 0
UNION
SELECT
    '' AS box,
    codes.code AS item,
    '' AS parent,
    codes.code AS code,
    codes.dept AS codedept,
    codes.type AS codestype,
    codes.description AS codedesc,
    '' AS make,
    '' AS model,
    '' AS name,
    '' AS serial,
    '' AS status,
    '' AS location,
    orders.job AS job,
    SUM(fulfils.quantity) AS quantity
FROM
    fulfils
    LEFT JOIN orderitems ON orderitems.id = fulfils.orderitem
    LEFT JOIN orders ON orders.id = orderitems.order
    LEFT JOIN codes ON codes.code = fulfils.code
WHERE
    orders.job = 'TEST'
GROUP BY
    fulfils.code,
    codes.dept,
    codes.type,
    codes.description,
    orders.job
HAVING
    quantity > 0;
  

Выдает (оставшиеся 5 должны равняться 2):

  ------- ----------- ----- ---------- 
|  box  |   item    | ... | quantity |
 ------- ----------- ----- ---------- 
| BOX01 | PRODUCT01 | ... |        3 |
|       | PRODUCT01 | ... |        5 |
 ------- ----------- ----- ---------- 
  

Думаю, я взломал это:

 SELECT
    stockbox.box AS box,
    codes.code AS item,
    stockbox.box AS parent,
    codes.code AS code,
    codes.dept AS codedept,
    codes.type AS codestype,
    codes.description AS codedesc,
    '' AS make,
    '' AS model,
    '' AS name,
    '' AS serial,
    '' AS status,
    '' AS LOCATION,
    boxes.job AS job,
    SUM(stockbox.quantity) AS quantity
FROM
    stockbox
    LEFT JOIN codes ON codes.code = stockbox.code
    LEFT JOIN boxes ON boxes.box = stockbox.box
WHERE
    boxes.job = 'TEST'
GROUP BY
    stockbox.box,
    codes.code,
    codes.dept,
    codes.type,
    codes.description,
    boxes.box
HAVING
    quantity > 0
UNION
SELECT
    '' AS box,
    codes.code AS item,
    '' AS parent,
    codes.code AS code,
    codes.dept AS codedept,
    codes.type AS codestype,
    codes.description AS codedesc,
    '' AS make,
    '' AS model,
    '' AS name,
    '' AS serial,
    '' AS status,
    '' AS LOCATION,
    orders.job AS job,
    SUM(fulfils.quantity) - (
        SELECT SUM(moo.quantity)
        FROM (
            SELECT
                stockbox.quantity AS quantity
            FROM
            stockbox
            LEFT JOIN boxes boxes2 ON boxes2.box = stockbox.box
            WHERE
                boxes2.job = 'TEST'
            ) AS moo
        ) AS quantity
FROM
    fulfils
    LEFT JOIN orderitems ON orderitems.id = fulfils.orderitem
    LEFT JOIN orders ON orders.id = orderitems.order
    LEFT JOIN codes ON codes.code = fulfils.code
WHERE
    orders.job = 'TEST'
GROUP BY
    fulfils.code,
    codes.dept,
    codes.type,
    codes.description,
    orders.job
HAVING
    quantity > 0;
  

Кажется, это позволяет достичь того, что мне нужно, и дало мне лучшее понимание суммирования вложенных выборок.

Любые другие решения приветствуются.