我正在寻找一种解决方案，以每年计算一个日期范围内的天数。我的桌子看起来像这样：
+----+-----------+------------+------------+
| id | source_id | start_date | end_date |
+----+-----------+------------+------------+
| 1 | 1 | 2015-11-01 | 2017-01-31 |
+----+-----------+------------+------------+
现在我要计算一下之间的天数。使用DATEDIFF（）轻松完成，但是每年如何做呢？
我尝试了一种临时工。转换为单行以执行计数和分组操作：
+----+-----------+------------+------------+
| id | source_id | start_date | end_date |
+----+-----------+------------+------------+
| 1 | 1 | 2015-11-01 | 2015-12-31 |
+----+-----------+------------+------------+
| 1 | 1 | 2016-01-01 | 2016-12-31 |
+----+-----------+------------+------------+
| 1 | 1 | 2017-01-01 | 2017-01-31 |
+----+-----------+------------+------------+
编辑：
所需的输出应为：
+-----------+------+------+
| source_id | year | days |
+-----------+------+------+
| 1 | 2015 | 60 |
+-----------+------+------+
| 1 | 2016 | 365 |
+-----------+------+------+
| 1 | 2017 | 30 |
+-----------+------+------+
因此，可以汇总按source_id和year分组的所有天。
在MySQL中有简单的方法吗？
最佳答案
创建另一个列出所有年份的表：
CREATE TABLE years (
year_start DATE,
year_end DATE
);
INSERT INTO years VALUES
('2015-01-01', '2015-12-31'),
('2016-01-01', '2016-12-31'),
('2017-01-01', '2017-12-31');
然后你可以加入这张桌子
SELECT t.source_id, YEAR(y.year_start) AS year, DATEDIFF(LEAST(year_end, end_date), GREATEST(year_start, start_date)) AS day_count
FROM yourTable AS t
JOIN years AS y
ON y.year_start BETWEEN t.start_date AND t.end_date
OR y.year_end BETWEEN t.start_date AND t.end_date
DEMO
如果您不想创建一个真实的表，可以使用一个子查询来动态创建它：
SELECT t.source_id, YEAR(y.year_start) AS year, DATEDIFF(LEAST(year_end, end_date), GREATEST(year_start, start_date)) AS day_count
FROM yourTable AS t
JOIN (SELECT CAST('2015-01-01' AS DATE) AS year_start, CAST('2015-12-31' AS DATE) AS year_end
UNION
SELECT CAST('2016-01-01' AS DATE) AS year_start, CAST('2016-12-31' AS DATE) AS year_end
UNION
SELECT CAST('2017-01-01' AS DATE) AS year_start, CAST('2017-12-31' AS DATE) AS year_end
) AS y
ON y.year_start BETWEEN t.start_date AND t.end_date
OR y.year_end BETWEEN t.start_date AND t.end_date
DEMO