mysql - 计算日期范围内的天数，但不包括重叠天数

我在找几个日期范围内的天数。我使用datediff函数来计算天数的总和，但是现在我想排除重叠的天数。因此，从最早的日期到当前日期，我希望日期范围内的天数，如果在重叠范围内，则每天只计算一次。
我的桌子是这样的：

Person_id |      Start_date      | End_date              | Count
 83244       2014-09-01 00:00:00    2014-09-06 00:00:00    5
 83244       2014-09-08 00:00:00    2015-09-07 00:00:00    364
 83244       2015-01-15 00:00:00    2015-02-01 00:00:00    17

如果我把这个加起来，我会得到382，但我要找的答案是369。因为最后一行与第二行完全重叠。
有人有解决办法吗？

最佳答案

我用第二个Person_id填充了您的示例，并将列名缩短了一点，使代码更短了一点：

CREATE TABLE tbl(`pid` int, `sd` datetime, `ed` datetime);
INSERT INTO tbl (`pid`, `sd`, `ed`)
VALUES
    (83244, '2014-09-01', '2014-09-06'),
    (83244, '2014-09-08', '2015-09-07'),
    (83243, '2014-08-08', '2015-08-15'),
    (83243, '2014-08-11', '2015-09-03'),
    (83244, '2015-01-15', '2015-02-01');

因此，在处理上述数据时，可以应用以下查询：

SELECT pid,sd,ed,CASE WHEN @id!=pid THEN @id:=pid+0*(@ed:=Date('1970-1-1')) END id,
       CASE WHEN sd<@ed THEN CASE WHEN ed>@ed THEN datediff(ed,@ed) ELSE 0 END
                        ELSE datediff(ed,sd) END days,
       @ed:=CASE WHEN ed>@ed THEN ed ELSE @ed END enddt
FROM tbl,( select @id:=0 ) const
ORDER BY pid,sd

与其他RDBMS相反，MySql在处理select语句时有一定的“过程感”。实际上，您可以在其中使用变量（@id和@ed），这些变量将随时间改变其状态（在这种情况下，末尾的order by子句非常重要）。
这个查询背后的基本思想是：从某个pid开始，并按开始日期的增加顺序列出间隔（sd）。永远记住变量ed中的结束日期（@ed）的最大值。现在，用每一个新的间隔，检查是否与上一个间隔有重叠，即检查当前开始日期sd是否小于先前（最大）结束日期（@ed），并相应地计算间隔days。
第一个case子句必须在当前@id更改时重置变量@ed和pid。
Subqueryconst只是在开头设置变量@id。
查询产生以下结果：

  pid   sd                  ed                  id     days enddt
83243   2014-08-08 00:00:00 2015-08-15 00:00:00 83243   372 2015-08-15 00:00:00
83243   2014-08-11 00:00:00 2015-09-03 00:00:00          19 2015-09-03 00:00:00
83244   2014-09-01 00:00:00 2014-09-06 00:00:00 83244     5 2014-09-06 00:00:00
83244   2014-09-08 00:00:00 2015-09-07 00:00:00         364 2015-09-07 00:00:00
83244   2015-01-15 00:00:00 2015-02-01 00:00:00           0 2015-09-07 00:00:00

有关Demo的信息，请参见此处。
如果您只是对总和感兴趣，那么您当然可以将整个查询包装成另一个这样的查询：

SELECT pid,sum(days) FROM (
 SELECT pid,sd,ed,CASE WHEN @id!=pid THEN @id:=pid+0*(@ed:=Date('1970-1-1')) END id,
        CASE WHEN sd<@ed THEN CASE WHEN ed>@ed THEN datediff(ed,@ed) ELSE 0 END
                         ELSE datediff(ed,sd) END days,
        @ed:=CASE WHEN ed>@ed THEN ed ELSE @ed END enddt
 FROM tbl,( select @id:=0 ) const
 ORDER BY pid,sd
) t GROUP BY pid ORDER BY pid

这样你就可以

pid     sum(days)
83243   391
83244   369

关于mysql - 计算日期范围内的天数，但不包括重叠天数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/36217453/