为了得到时间来完成一个特定的里程碑的所有命令，你可以做

select orderid, 
       DATEDIFF(hh, min(TIMESTAMP), max(TIMESTAMP))
from your_table
where ORDERMILESTONE = 'mile stone name'
group by orderid

假设您使用的是sql server，并且里程碑没有重复，那么您可以使用：

select om.orderid, 
       datediff(seconds, min(timestamp), max(timestamp))
from order_milestones om
where milestone in ('milestone1', 'milestone2')
group by om.orderid;

如果您想在每一行上更普遍地执行此操作，可以使用累积聚合函数：

select om.*, 
       datediff(seconds,
                timestamp, 
                min(case when milestone = 'order' then timestamp end) over 
                    (partition by orderid
                     order by timestamp
                     rows between current row and unbounded following
                    )
               ) as time_to_order
from order_milestones om
group by om.orderid;

您可以创建一个查找表，其中包含一个里程碑并给出上一个里程碑。然后，可以左键联接到它，左键联接回原始表，以获取上一个里程碑处相同顺序的行，并比较日期（sqlfiddle）：

select om.*, datediff(minute, pm.TIMESTAMP, om.TIMESTAMP) as [Minutes]
from OrderMilestones om
left join MilestoneSequence ms on ms.ORDERMILESTONE = om.ORDERMILESTONE
left join OrderMilestones pm on pm.ORDERID = om.ORDERID
      and pm.ORDERMILESTONE = ms.PREVIOUSMILESTONE
order by om.TIMESTAMP