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
FROM orders
-- and some other joins here for additional report data

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

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

我尝试在SELECT子句中添加一个DATEDIFF(final_delivery_projection, initial_delivery_projection)列，以查看给定的交付预测提前了多少天，但是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

字段列表中的未知列Final_Delivery_Projection

如果别名在同一语句中引用SUBSELECT，则可能不能在SELECT语句中使用别名。

我还希望能够根据预测结果排除WHERE子句中的订单。例如，我想排除所有最终预计发货日期在初始预计日期之前的订单，理由是我只对被推迟的订单感兴趣，而不是提前发货的订单。

是否可以在同一语句中提取和处理我尝试获取的数据，或者我是否需要在客户端中进行一些后处理才能解决这样的问题？如果这在SQL中是可能的，那么如何才能做到这一点呢？

---

解决方案

我花了很多时间研究这个问题，这个问题是mysql的一个基本问题，即如果内部查询位于查询的FROM部分，则不能在内部查询中引用外部查询，即使内部查询位于SELECT部分，也可以引用外部查询。

至于从交付日期历史记录中获取第一条和最后一条记录，我发现了这个查询，它能以可接受的性能生成正确的结果。

SELECT odr.order_id,
    first_change.projected_date AS initial_projected_dispatch_date,
    last_change.projected_date AS final_projected_dispatch_date
FROM order AS odr
LEFT JOIN (
    SELECT 
        order_id, 
        MIN(order_delivery_projection_id) AS first_id, 
        MAX(order_delivery_projection_id) AS last_id
    FROM order_delivery_projections
    GROUP BY order_id
) AS change_record_finder ON change_record_finder.order_id = odr.order_id
LEFT JOIN order_delivery_projections AS first_change ON first_change.order_delivery_projection_id = change_record_finder.first_id
LEFT JOIN order_delivery_projections AS last_change ON last_change.order_delivery_projection_id = change_record_finder.last_id
WHERE -- where clauses go here

Matt的解决方案本来可以在其他SQL版本中工作，但是MySQL缺乏所需的功能，无法让他的正确答案工作。