嘿，我来帮你搞定这个时间差计算的需求！根据你描述的场景——要匹配相同PO_NO、MANAGEMENT_NO、PROCESS_NAME的Start和Finish记录，算出两者的分钟差，我分两种常见场景给你解决方案：

场景1：所有数据在同一张表中 #

看你给的示例数据，看起来像是单表存储了Start和Finish状态的记录，这种情况用自连接就能轻松匹配同组的开始和完成记录：

MySQL 示例查询 #

SELECT
    s.ID AS Start_ID,
    f.ID AS Finish_ID,
    s.PO_NO,
    s.MANAGEMENT_NO,
    s.PROCESS_NAME,
    s.Time_Occurrence AS Start_Time,
    f.Time_Occurrence AS Finish_Time,
    TIMESTAMPDIFF(MINUTE, s.Time_Occurrence, f.Time_Occurrence) AS TimeDiff_Minute
FROM
    your_table s
JOIN
    your_table f ON 
        s.PO_NO = f.PO_NO
        AND s.MANAGEMENT_NO = f.MANAGEMENT_NO
        AND s.PROCESS_NAME = f.PROCESS_NAME
        AND s.STATUS = 'Start'
        AND f.STATUS = 'Finish'

关键说明 #

• 用别名s（Start）和f（Finish）区分同一张表的两类记录，通过三个核心字段关联
• 不同数据库计算时间差的函数略有不同：
  • PostgreSQL：用EXTRACT(EPOCH FROM (f.Time_Occurrence - s.Time_Occurrence)) / 60来转成分钟
  • SQL Server：直接用DATEDIFF(MINUTE, s.Time_Occurrence, f.Time_Occurrence)

场景2：数据分在两张表（当前状态表+完成状态表） #

如果Start状态存在current_status表，Finish状态存在completed_status表，只需要把自连接改成跨表关联即可：

通用SQL示例 #

SELECT
    cs.ID AS Start_ID,
    cp.ID AS Finish_ID,
    cs.PO_NO,
    cs.MANAGEMENT_NO,
    cs.PROCESS_NAME,
    cs.Time_Occurrence AS Start_Time,
    cp.Time_Occurrence AS Finish_Time,
    TIMESTAMPDIFF(MINUTE, cs.Time_Occurrence, cp.Time_Occurrence) AS TimeDiff_Minute
FROM
    current_status cs
JOIN
    completed_status cp ON 
        cs.PO_NO = cp.PO_NO
        AND cs.MANAGEMENT_NO = cp.MANAGEMENT_NO
        AND cs.PROCESS_NAME = cp.PROCESS_NAME
WHERE
    cs.STATUS = 'Start'
    AND cp.STATUS = 'Finish'

处理特殊情况 #

如果你的数据存在一些异常场景，比如同组有多条Start/Finish记录，或者某组只有Start没有Finish，这里也给你对应的处理方案：

1. 同组有多条Start/Finish记录 #

用窗口函数ROW_NUMBER()给同组的记录按时间排序，取最新的Start对应最新的Finish（MySQL 8.0+支持）：

WITH ranked_start AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY PO_NO, MANAGEMENT_NO, PROCESS_NAME ORDER BY Time_Occurrence DESC) AS rn
    FROM your_table
    WHERE STATUS = 'Start'
),
ranked_finish AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY PO_NO, MANAGEMENT_NO, PROCESS_NAME ORDER BY Time_Occurrence DESC) AS rn
    FROM your_table
    WHERE STATUS = 'Finish'
)
SELECT
    rs.PO_NO,
    rs.MANAGEMENT_NO,
    rs.PROCESS_NAME,
    rs.Time_Occurrence AS Start_Time,
    rf.Time_Occurrence AS Finish_Time,
    TIMESTAMPDIFF(MINUTE, rs.Time_Occurrence, rf.Time_Occurrence) AS TimeDiff_Minute
FROM ranked_start rs
JOIN ranked_finish rf ON 
    rs.PO_NO = rf.PO_NO
    AND rs.MANAGEMENT_NO = rf.MANAGEMENT_NO
    AND rs.PROCESS_NAME = rf.PROCESS_NAME
    AND rs.rn = rf.rn

2. 存在缺失状态的记录 #

如果某组只有Start没有Finish（或反之），把JOIN改成LEFT JOIN，这样缺失的记录会显示NULL，还可以用COALESCE把NULL替换成0或者其他标记值：

SELECT
    s.PO_NO,
    s.MANAGEMENT_NO,
    s.PROCESS_NAME,
    s.Time_Occurrence AS Start_Time,
    f.Time_Occurrence AS Finish_Time,
    COALESCE(TIMESTAMPDIFF(MINUTE, s.Time_Occurrence, f.Time_Occurrence), 0) AS TimeDiff_Minute
FROM
    your_table s
LEFT JOIN
    your_table f ON 
        s.PO_NO = f.PO_NO
        AND s.MANAGEMENT_NO = f.MANAGEMENT_NO
        AND s.PROCESS_NAME = f.PROCESS_NAME
        AND s.STATUS = 'Start'
        AND f.STATUS = 'Finish'
WHERE s.STATUS = 'Start'

内容的提问来源于stack exchange，提问作者Cát Tường Vy