我有一个活动表,它包含“开始”和(以及许多其他)“结束”事件。

person, datetime, event
1, 2015-01-1 13:30, start
2, 2015-01-1 13:33, start
3, 2015-01-1 13:38, start
2, 2015-01-1 13:57, end
1, 2015-01-1 14:03, end
1, 2015-01-1 14:07, start
1, 2015-01-1 14:12, end


我希望检索它们是这样的:

person, start, end
1, 2015-01-01 13:30, 2015-01-01 14:03
1, 2015-01-01 14:07, 2015-01-01 14:12
2, 2015-01-01 13:33, 2015-01-01 13:57
3, 2015-01-01 13:38, null


我似乎无法将关键字组合在一起,以找到这种“按行组合”机制或类似情况。

最佳答案

没有子查询

SELECT startevent.person_id as person,
       startevent.datetime as [start],
       min(endevent.datetime) as [end]
  FROM person_events as startevent
  join person_events as endevent
    on startevent.person_id = endevent.person_id
   and startevent.event = 'start'
   and endevent.event =   'end'
   and startevent.datetime < endevent.datetime
 GROUP BY startevent.person_id, startevent.datetime
 ORDER BY startevent.person_id, startevent.datetime

10-05 23:06
查看更多