MySQL:引用子查询列

Question

我目前正在为电子商务系统编写一些报告，该系统需要显示订单延迟了多长时间。我有一份订单的预计交货日期日志，并且能够获得初始日期和最后日期，但是做更复杂的事情(例如比较预计日期)证明是有问题的。

我的查询(简化版)如下:

SELECT orders.order_id,
       orders.date_dispatched AS actual_dispatch_date,
       (
           SELECT projected_date 
           FROM order_delivery_projections
           WHERE order_id = orders.order_id
           ORDER BY order_delivery_projection_id ASC
           LIMIT 1
       ) AS initial_delivery_projection,
       (
           SELECT projected_date 
           FROM order_delivery_projections
           WHERE order_id = orders.order_id
           ORDER BY order_delivery_projection_id DESC
           LIMIT 1
       ) AS final_delivery_projection
FROM orders
-- and some other joins here for additional report data

(仅供引用，我在这里按 ID 而不是日期排序，因为发货预测可以向前也可以向后移动，例如，如果库存发货提前到达)。

这对于提取有关订单预计交货历史的一些原始数据很好，但我想对数据做一些额外的分析，例如初始预计发货日期与最终预计发货日期之间有多少天的差异装运日期和实际装运日期。这是我遇到麻烦的地方。

我尝试将 DATEDIFF(final_delivery_projection, initial_delivery_projection) 列添加到我的 SELECT 子句中，以便查看给定的交付预测已经推迟了多少天，但 MySQL 没有。

SELECT orders.order_id,
       orders.date_dispatched AS actual_dispatch_date,
       (
           SELECT projected_date 
           FROM order_delivery_projections
           WHERE order_id = orders.order_id
           ORDER BY order_delivery_projection_id ASC
           LIMIT 1
       ) AS initial_delivery_projection,
       (
           SELECT projected_date 
           FROM order_delivery_projections
           WHERE order_id = orders.order_id
           ORDER BY order_delivery_projection_id DESC
           LIMIT 1
       ) AS final_delivery_projection,
       DATEDIFF(final_delivery_projection - initial_delivery_projection) AS projection_days_revised
FROM orders
-- and some other joins here for additional report data

Unknown column final_delivery_projection in field list

如果别名指的是同一语句中的子选择，则您不能在 select 语句中使用别名。

我还希望能够根据预测结果排除 WHERE 子句中的订单。例如，我想排除所有最终预计发货日期早于初始预计发货日期的订单，因为我只对被搁置的订单感兴趣，而不是那些提前发货的订单。

是否可以在同一条语句中提取和处理我尝试获取的数据，或者我是否必须在客户端中进行一些后处理才能解决此类问题？如果在 SQL 中可以实现，那么如何实现呢？

Answer 1

我相信选择的值是同时生成的。因此可能无法计算日期差异，因为当时可能不知道别名值。如果重构 Select，您可能会得到想要的结果。此外，您还可以访问其他表值，以便对子查询/数据集进行额外分析。

SELECT orders.order_id,
       orders.date_dispatched AS actual_dispatch_date,
       First.Projected_Date AS initial_delivery_projection,
       Current.Projected_Data AS final_delivery_projection,
       dateDiff(Current.Projected_date,First.Projected_date)
FROM orders O
LEFT JOIN (SELECT projected_date, Order_ID 
           FROM order_delivery_projections
           ORDER BY order_delivery_projection_id ASC
           LIMIT 1) First
    ON First.order_id = O.order_id
LEFT JOIN (SELECT projected_date, Order_ID
           FROM order_delivery_projections
           ORDER BY order_delivery_projection_id DESC
           LIMIT 1)
    ON Current.Order_ID = O.Order_ID

Orders 中的 Order_ID 是否可能不存在于 order_Delivery_projections 中？如果是这样，那么空值上的日期差异也可能导致错误......