用于获取订单状态的复杂sql查询

h6my8fg2 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(347)

table

所以，我有一个 line_items 与 orders table。显然是单身 order 可以有多个 line_item s。
一 line_item （除其他外）有 item_status . 所有的 item_status 在一个叫做， item_statuses . 有一个 stage 表中表示状态线性性质的列。所以，一个 item_status 的 proofing 有一个 stage 的 20 ，因为它在生产过程中比，比如说， printing ，即 60 . shipping 是 80 等等。你明白了。它表示 line_item 的状态。
所以，我很容易就能知道 line_item 这是注定的 order 根据他们的 item_status . 一 order 可能有2个 line_item 在房间里的 shipping 部门，但还有一项 printing . 到目前为止有意义吗？

景色

作为我最终目标的一步，我需要确定一个 order 的“状态”。为此，我决定 order 的“状态”将与最早的相同 item_status 所有的 line_item s(我用引号表示 order 的“状态”，因为它从来没有真正存储在任何地方，只是计算的飞行。一个视图将有助于存储它。）
所以，如果 order 有3个 line_item s、与 item_status 的es printing , shipping ，和 shipping ，然后是整体 order 的“状态”应该是 printing ，因为它有一个 line_item 这还停留在以前 item_status . (自 printing 具有较低的 stage 中的数字 item_statuses 表 shipping .)
我想创建一个视图，让我 order 中所有记录的编号及其“状态” orders table。

目标

最后，我需要数一数 order 他们的 ship_date （这也是 orders 表），但按其“状态”细分(例如，一个给定的 ship_date 会有78个 order 那是什么 shipping , 139 printing ，和43 proofing . 我认为创建我提到的视图将是实现这一目标的一块垫脚石。）

进展

到目前为止，我只能想到这些：

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 对于一个单一的，硬编码的 order ，则仅显示具有该“状态”的订单。我需要这个能够显示我所有的命令和他们相关的“状态”的。

问题

所有这一切只是为了让我有一部分的方法来实现我的目标。正如我前面提到的，我最终需要这个来获得所有订单的计数，以日期分隔（在一周范围内），并查看当天每个“状态”中有多少订单。
最糟糕的是我知道这应该是可能的；甚至可能有一个优雅的解决方案，我只是看不到。因此，我来这里是万不得已。

sql mysql

来源：https://stackoverflow.com/questions/50994306/complex-sql-query-for-getting-the-status-of-orders

2条答案

按热度按时间

eagi6jfj1#

它（有点）比你想象的简单。让我们把它建起来。
首先，让我们为所有项目准备舞台：

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

（如果此时需要阶段名称，最好将整个查询推入子查询中，然后再次联接名称，否则必须将名称作为额外的列添加到分组中。这是留给读者的练习。）
如果要限制日期范围，可以将其包含在视图中，但我可能会将其关闭，只需添加 WHERE 子句查询视图本身时：

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

精明的读者会注意到在日期范围中有空白。连接到日历表（获得缺少日期的推荐方法）-无论是否在视图中-也留给读者作为练习。

赞(0）回复(0）举报 2021-06-21

hkmswyz62#

你的问题没有你的职位那么复杂。：）
我想你想要的是：

SELECT 
  tbl.ship_date_id,
  tbl.order_id,
  tbl.item_status_id order_status
FROM
(
  SELECT 
    orders.ship_date_id,
    orders.id order_id,
    item_statuses.id item_status_id,
    ROW_NUMBER()OVER(PARTITION BY orders.id ORDER BY item_statuses.id ASC) rn
  FROM orders 
  JOIN line_items     ON line_items.order_id = orders.id
  JOIN item_statuses  ON item_statuses.id = line_items.item_status_id
) tbl
WHERE tbl.rn = 1

然后你就可以根据发货日期做统计了
这应该是在这样的假设下进行的 item_statuses.id 是一个随着进程的进行而递增的数字（即。 printing 有3号身份证 shipping 具有id 4，等等）说明：row_number函数将根据item_statuses.id` 我们只需要在这个过程的最早阶段为这个项目取一行。

赞(0）回复(0）举报 2021-06-21

我来回答

用于获取订单状态的复杂sql查询

table

景色

目标

进展

问题

2条答案

相关问题

热门标签

最新问答