MySQL如何获取可能有重叠日期的开始/结束日期的总和 [英] MySQL how to get a sum of start/end dates with possible overlapping dates

问题描述

我有一个开始/结束日期表，我想按ID分组并汇总每个ID的总时间.例如:

I have a table of start/end dates that I want to group by id and sum the total time for each id. For example:

fk_id            start              end
3                2014-03-21 10:02   2014-05-01 08:05
3                2014-06-05 05:00   2014-06-20 22:00  
5                2014-03-10 08:00   2014-06-20 13:50  
5                2014-05-10 09:45   2014-06-22 15:31

对于fk_id=3，没有问题:

• 我可以为每一行做一个TIMESTAMPDIFF并将它们相加

但是对于fk_id=5，日期重叠:

• 因此，在这种情况下，TIMESTAMPDIFF应该在
  • 2014-03-10 08:00
  • 2014-06-22 15:31
  • so in that case the TIMESTAMPDIFF should be done between
    • 2014-03-10 08:00
    • 2014-06-22 15:31

    在MySQL中有什么方法可以进行这种类型的查询吗?

    Is there any way to do this type of query in MySQL?

    谢谢！

    推荐答案

    使用变量在汇总总数之前先确定范围

    Use variable to fix the ranges before agregate the totals

    SQL DEMO

    SQL DEMO

    SELECT t.`fk_id`,
           @rn := if(@id = `fk_id`, 
                     @rn + 1, 
                     if(@id:=`fk_id`, if(@end:='1900-01-01 00:00:00',1,1), if(@end:='1900-01-01 00:00:00',1,1))   
                    ) as rn,
           if(start < @end, 
              @end, 
              if(@end := end, `start`, `start`) 
             ) as `start`, 
           end
    FROM Table1 t
    CROSS JOIN (SELECT @id := 0, @end := STR_TO_DATE('1900-01-01 00:00:00', '%Y-%m-%d %H:%i:%s') as e, @rn := 0) t
    ORDER BY `fk_id`, `start`
    

    输出

    这篇关于MySQL如何获取可能有重叠日期的开始/结束日期的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！