MySQL查询以获得开始日期和结束日期之间的差额

Question

我有一张表，其中有以下数据：

+-----------+-----------+
| Date_from | Date_to   |
+-----------+-----------+
| 20-NOV-19 | 22-NOV-19 |
+-----------+-----------+
| 10-NOV-19 | 21-NOV-19 |
+-----------+-----------+
| 14-NOV-19 | 26-NOV-19 |
+-----------+-----------+

我需要一个查询来查找date_from和date_to之间的差异之和。

示例：

• 20-11月19日和22-11月19日之间的差异是2天
• ，10-11月19和21-11月19之间的差异是11天，但是查询必须将其视为9天，因为第一行已经考虑了20-11月和21-11月。
• 14-11月19日和26-11月19日之间的区别是12天，但查询必须将其视为4天，因为在上面的行中已经考虑了14-11月14日至22-11月。

查询结果应该是

15天(2+9+4)

任何帮助都将不胜感激。

Answer 1

您可以使用下面的查询获得总计。请用您的实际表名更改Table_name

SELECT SUM(TIMESTAMPDIFF(DAY,Date_from,Date_to)) as total FROM Table_name 

Answer 2

我在一个子查询中使用了窗口函数来计算date_from和date_to之间的差异，然后减去任何重叠的天数。

SELECT SUM(days) FROM
    (SELECT CASE WHEN LEAD(date_from) OVER w  < date_to THEN DATEDIFF(date_to, date_from) + DATEDIFF(LEAD(date_from) OVER w, date_to)
          ELSE DATEDIFF(date_to, date_from)
          END AS days
          FROM test
          WINDOW w AS (ORDER BY date_to)) as d

请注意，这会产生16天的结果，而不是问题中的15天，但同样如此。

SELECT DATEDIFF('2019-11-26', '2019-11-10')

Answer 3

您可以使用CTE生成范围之间的所有日期，并获得distinct count().It将给出作为17的总天数。因为你想要差，所以你必须减去2(开始日期和结束日期)。

WITH recursive Date_Ranges AS (
    select datefrom as Dt,dateto from mydates
   union all
   select dt + interval 1 day , dateto
   from Date_Ranges
   where dt < dateto)
select count(distinct(dt))-2 from Date_Ranges