mysql - 用于获取订单状态的复杂 SQL 查询

Question

表格

因此，我有一个与 orders 表链接的 line_items 表。显然，一个 order 可以有多个 line_item。

line_item 具有(除其他外)item_status。所有 item_status 都列在一个自然地称为 item_statuses 的表中。该表中有一个阶段列，它指示状态的线性性质。因此，proofing 的 item_status 的 stage 为 20，因为它在生产管道中出现的时间早于:比如说，打印，即60。 shipping 是 80 等。您明白了。它指示 line_item 在生产管道中的状态。

因此，我可以轻松查看给定订单有多少个line_item，并按item_status对它们进行分组。一个订单可能有 2 个 line_item 位于 shipping 部门，但有 1 个项目仍在打印。到目前为止有意义吗？

View

作为实现最终目标的一步，我需要确定订单的“状态”。为此，我决定订单的“状态”将与其所有最早的item_status相同>line_items。 (我对订单的“状态”使用引号，因为它从未真正存储在任何地方，只是动态计算。 View 将有助于存储它。)

因此，如果一个 order 有 3 个 line_item，且 item_status 为 printing， Shipping 和 shipping，那么整个订单的“状态”应该是printing，因为它有一个 line_item 仍然停留在之前的 item_status 状态。 (因为 item_statuses 表中 printing 的 stage 编号低于 shipping。)

我想创建一个 View ，该 View 将为我提供订单中所有记录的订单编号及其“状态”表。

目标

最终，我需要按 ship_date(这也是 中的一列)获取所有订单的计数>orders 表)，但按其“状态”分割。 (例如，给定的 ship_date 将有 78 个订单，其中 shipping、139 个 printing 和43 校对。我认为创建我提到的 View 将是实现这一目标的垫脚石。)

进展

到目前为止，这就是我所能想到的:

SELECT
  orders.ship_date_id,
  orders.id,
  item_statuses.id
FROM item_statuses
  JOIN line_items
    ON item_statuses.id = line_items.item_status_id
  JOIN orders
    ON line_items.order_id = orders.id
WHERE item_statuses.stage = (
  SELECT MIN(item_statuses.stage)
  FROM item_statuses
    JOIN (
           SELECT line_items.item_status_id
           FROM line_items
             JOIN orders
               ON line_items.order_id = orders.id
           WHERE orders.id = '521079'
         ) AS x
      ON x.item_status_id = item_statuses.id
)

太丑了。这很复杂。它所做的只是获取单个硬编码订单的“最早”item_status，然后仅显示具有该“状态”的订单。我需要它能够向我显示所有订单及其相关的“状态”。

问题

所有这一切只是为了让我实现目标的一部分。正如我之前提到的，我最终将需要它来获取所有订单的计数，按日期分隔(一周范围内)，并查看当天每个“状态”中有多少订单。

最糟糕的是我知道这应该是可能的；甚至可能有一个我看不到的优雅解决方案。因此，我来​​到这里作为最后的手段。

Answer 1

它(稍微)比您想象的简单。让我们来构建它。

首先，让我们获得所有项目的舞台:

SELECT Line_Items.order_id, Item_Statuses.stage
FROM Line_Items
JOIN Item_Statuses
  ON Item_Statuses.id = Line_Items.item_status_id

这给出了(可能)看起来像这样的东西:

1 | 20
1 | 39
2 | 50

好吧，现在，由于每个订单都有多行，因此我们需要每个订单的最小阶段。简单聚合:

SELECT Line_Items.order_id, MIN(Item_Status.stage) AS stage
FROM Line_Items
JOIN Item_Statuses
  ON Item_Statuses.id = Line_Items.item_status_id
GROUP BY Line_Items.order_id

这会产生:

1 | 20
2 | 50

现在，由于您需要发货日期，我们需要将 Orders 表连接到上一个查询的整个结果集。这意味着我们需要一个子查询:

SELECT Orders.id, Orders.ship_date_id,
       Order_Status.stage,
FROM Orders
LEFT JOIN (SELECT Line_Items.order_id, MIN(Item_Status.stage) AS stage
           FROM Line_Items
           JOIN Item_Statuses
             ON Item_Statuses.id = Line_Items.item_status_id
           GROUP BY Line_Items.order_id) AS Order_Status
       ON Order_Status.order_id = Orders.id

这会产生:

1 | "2015-01-01" | 20
2 | "2015-01-04" | 50
3 | "2015-01-05" | (null) -- you might have orders without line items!

不清楚你是否想要/有阶段的文字描述。如果是这样，您还需要再次加入状态表:

SELECT Orders.id, Orders.ship_date_id,
       Item_Statuses.stage,
FROM Orders
LEFT JOIN (SELECT Line_Items.order_id, MIN(Item_Status.stage) AS stage
           FROM Line_Items
           JOIN Item_Statuses
             ON Item_Statuses.id = Line_Items.item_status_id
           GROUP BY Line_Items.order_id) AS Order_Status
       ON Order_Status.order_id = Orders.id
JOIN Item_Statuses
  ON Item_Status.stage = Order_Status.stage

(这个特定版本假设 stage 是一个唯一的值 - 如果不是，那么您还会遇到其他问题)

1 | "2015-01-01" | 'Printing'
2 | "2015-01-04" | 'Proofing'
3 | "2015-01-05" | (null) -- you might have orders without line items!

<小时/>

如何获取订单数量？

嗯，获取特定日期的订单数量很容易:

SELECT Orders.ship_date_id, COUNT(*) as orders
FROM Orders
GROUP By Orders.ship_date_id

"2015-01-01" | 1
"2015-01-04" | 400
"2015-04-05" | 33

然后，您可以将两个查询合并在一起:

SELECT Orders.ship_date_id, Order_Status.stage,
       COUNT(*) AS orders
FROM Orders
LEFT JOIN (SELECT Line_Items.order_id, MIN(Item_Status.stage) AS stage
           FROM Line_Items
           JOIN Item_Statuses
             ON Item_Statuses.id = Line_Items.item_status_id
           GROUP BY Line_Items.order_id) AS Order_Status
       ON Order_Status.order_id = Orders.id
GROUP BY Orders.ship_date_id, Order_Status.stage

所以类似:

"2015-01-01" | 20     | 1 
"2015-01-04" | 30     | 200
"2015-01-04" | 40     | 200
"2015-04-05" | 40     | 2
"2015-04-05" | 20     | 30
"2015-04-05" | (null) | 1

(如果此时您需要阶段名称，最好将整个查询插入子查询并再次加入名称，因为否则您必须将名称添加为分组中的额外列。这将保留为供读者练习。)

如果您想限制日期范围，可以将其包含在 View 中，但我可能会保留它，并在查询 View 本身时添加 WHERE 子句:

SELECT ship_date_id, stage, orders
FROM Orders_Per_Day
WHERE ship_date_id >= :start
      AND ship_date_id < :end

精明的读者会注意到日期范围内存在间隙。加入日历表(获取缺失日期的推荐方法) - 无论是否在 View 中 - 也留给读者作为练习。