如何基于多表字段创建MySQL辅助表并计算订单时间差

要创建满足需求的辅助表，我们可以利用MySQL窗口函数为每个客户的到达（StatusID=11）和发货（StatusID=12）记录按时间顺序生成配对的Lap编号，再关联计算时间差。具体实现如下：

核心思路 #

1. 分别提取客户的到达、发货记录，为每个客户的同状态记录按时间排序生成Lap序号；
2. 通过客户ID+Lap序号将到达与发货记录一一配对；
3. 关联客户表获取姓名，计算到达与发货的分钟级时间差；
4. 将结果存入辅助表供后续导出。

SQL实现代码 #

CREATE TABLE Delivery_Report AS
WITH ArriveRecords AS (
    SELECT 
        CustomerID,
        Timestamp AS ArriveTime,
        ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY Timestamp) AS Lap
    FROM Deliverys_History
    WHERE StatusID = 11
),
DispatchRecords AS (
    SELECT 
        CustomerID,
        Timestamp AS DispatchTime,
        ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY Timestamp) AS Lap
    FROM Deliverys_History
    WHERE StatusID = 12
)
SELECT 
    c.CustomerID,
    c.CustomerName,
    a.Lap,
    a.ArriveTime AS `Arrive(11)`,
    d.DispatchTime AS `Dispatch(12)`,
    TIMESTAMPDIFF(MINUTE, a.ArriveTime, d.DispatchTime) AS `Diff (min)`
FROM ArriveRecords a
JOIN DispatchRecords d 
    ON a.CustomerID = d.CustomerID 
    AND a.Lap = d.Lap
JOIN Customers c 
    ON a.CustomerID = c.CustomerID
ORDER BY c.CustomerID, a.Lap;

代码说明 #

• ArriveRecords：筛选所有到达状态记录，按客户分组、时间排序，生成每个客户的到达记录序号（Lap）；
• DispatchRecords：筛选所有发货状态记录，按相同规则生成发货记录序号（Lap）；
• 关联计算：通过客户ID和Lap序号配对到达与发货记录，用TIMESTAMPDIFF函数计算分钟级时间差；
• 创建辅助表：通过CREATE TABLE ... AS将查询结果直接存入Delivery_Report表，后续可直接导出该表。

执行后，Delivery_Report表的结构和数据将与你提供的期望输出完全匹配。

内容的提问来源于stack exchange，提问作者Jailer Betancourt