mysql - 包含排除项目的多表条件 JOIN

Question

我正在创建一个设备租赁跟踪系统，用于管理分配给每个作业的库存，该库存也分配给箱子。

我有下表:

表格框(容器列表，在履行阶段分配给作业，作业返回时设置为空)

box
job
...

表代码(库存类型)

code
dept
type
description
...

表作业(jobs)

job
client
name
...

表订单(与工作相关的订单)

id
job
...

表格订单项目(与订单相关的项目)

id
order
code
quantity
...

表履行(履行订单项目，申请 + 发货数量，- 返回数量)

id
orderitem
code
quantity
...

表库存(+ 购买数量，- 处置数量)

id
code
quantity
...

表格库存盒(将 + 或 - 代码数量应用于盒子)

id
code
box
quantity
...

当 SUM(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;

这按预期工作。

我现在的挑战是根据工作分配和总库存箱分配(该箱子和代码的库存箱数量总和)将其分成多个箱子。另外，我需要包括未分配给任何盒子的履行总数(数量总和 - 分配给盒子的数量总和)。为此目的，我忽略了库存表，因为它不相关，只有stockbox。

假设框“BOX01”分配给作业“TEST”，“BOX01”的 SUM 为 3。作业“TEST”，通过表履行的代码“PRODUCT01”的 SUM 为 5，我希望看到这些运行 .job = 'TEST' 查询时的结果。

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

我完全愿意接受设计变更。一个简单的解决方法是在履行中添加“盒子”，但我希望在库存完成后可以自由地在盒子之间重新分配库存，而不影响履行表。

提前非常感谢。

更新:

这让我接近了，为分配给工作的每个盒子和其中的库存创建行。我现在需要从剩余履行数量中减去盒装数量的总和。

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 |
+-------+-----------+-----+----------+

Answer 1

我想我已经破解了它:

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;

它似乎达到了我的需要，并让我更好地理解了嵌套选择的求和。

非常感谢任何其他解决方案。