mysql - 计算 MySQL 表中时间戳内的天数

Question

假设我有一个包含以下记录的表:SQL Fiddle

| category |               begin |                 end |
|----------|---------------------|---------------------|
|        a | 2017-09-26 08:00:00 | 2017-09-27 10:00:00 |
|        b | 2017-10-02 13:00:00 | 2017-10-03 07:00:00 |
|        a | 2017-10-06 02:00:00 | 2017-10-06 09:00:00 |
|        a | 2017-10-06 11:00:00 | 2017-10-06 14:00:00 |
|        a | 2017-10-06 19:00:00 | 2017-10-08 10:00:00 |

我希望能够计算每个类别(在开始和结束之间)的不同天数的总数。因此:类别 a 包含 9 月 26 日至 27 日和 10 月 6 日至 8 日期间(其中 10 月 6 日出现在多个记录中)，而类别 b 仅包含 10 月 2 日至 3 日期间。因此，我希望得到以下结果:

| category | days |
|----------|------|
|        a |    5 |
|        b |    2 |

来自一些 GROUP BY category 语句。这在 (My)SQL 中可能吗？

Answer 1

一个可能的解决方案是构建一个“引用表”，其中包含适合您的情况的所有可能日期。 (例如，您可以将此填充到 future 20 年和过去 20 年。)为此 sqlfiddle ，我刚刚填写了必要的天数。我创建了带有单个日期列 day 的 ref_dates，并使用了以下 sql:

select category,
count(distinct day)
from times
inner join ref_dates 
    on cast(begin as date) <= day 
    and day <= cast(end as date)
group by category;