通过3个表格进行计算、求和和和分组

Question

我有4个表：项目、订单、inventory_item和库存。它们的结构如下：

_________         ____________         ________________         _____________
| orders  | >---- |  items     | ----< | inventory_item | >---- | inventories |
|- - - - -|       |- - - - - - |       |- - - - - - - - |       |- - - - - - -|
|+item_id |       |+id         |       |+id             |       |+id          |
|+amount  |       |+group_id   |       |+inventory_id   |        -------------
---------        |+worth      |       |+item_id        |       
|+min_amount |       |+amount         |
------------         ----------------

存货表不需要查询，只显示用于了解结构。

我想要一个与MariaDB兼容的sql查询，它可以执行以下操作：按库存和group_id显示分组：

• SUM(订单数量(
• SUM(items.min_amount(
• SUM(inventory_item.aunt(
• SUM(items.worth*inventory_item.aunt(

我试着用子查询来解决这个问题，但似乎没有希望。我目前的尝试如下：

SELECT
`inventory_item`.`inventory_id`,
`items`.`group_id`,
SUM(`items`.`min_amount`),
SUM(`inventory_item`.`amount`),
SUM(`inventory_item`.`amount` * `items`.`worth`),
SUM(`orders`.`amount`)
FROM `items`
INNER JOIN `inventory_item` ON `items`.`id` = `inventory_item`.`item_id`
LEFT JOIN `orders` ON `items`.`id` = `orders`.`item_id`
GROUP BY
`inventory_item`.`inventory_id`,
`items`.`group_id`

SUM(orders.amount(值实际上是正确的，但其他值是错误的，因为一个项目被计数了好几次。

我知道如何用多个查询来解决这个问题，但我只想用一个(MariaDB兼容(sql查询来解决。知道怎么做吗？

Answer 1

您需要的是沿着其中一个维度预先聚合结果。我认为您可以通过预聚合orders:来实现这一点

SELECT ii.inventory_id, i.group_id,
SUM(i.min_amount),
SUM(ii.amount),
SUM(ii.amount * i.worth),
SUM(i.amount)
FROM items i JOIN
inventory_item ii
ON i.id` = ii.item_id LEFT JOIN
(SELECT o.item_id, SUM(o.amount) as amount
FROM orders o
GROUP BY o.item_id
) o
ON o.item_id = i.id
GROUP BY ii.inventory_id, i.group_id