mysql - MySQL 范围内的每日图表

Question

我正在尝试制作一个图表，其中每一天都有一个点，显示每天出现的马匹数量。

这是我拥有的数据示例 (MySQL)

horse_id   |   start_date   |   end_date    |
1          |   2011-04-02   |   2011-04-03  |
2          |   2011-04-02   |   NULL        |
3          |   2011-04-04   |   2014-07-20  |
4          |   2012-05-11   |   NULL

因此，关于该数据的图表应该每天输出一行，从 2011-04-02 开始到 CURDATE 结束，对于每一天，它应该返回注册的马匹数量。

我不太清楚我将如何执行此操作，因为我只有每个项目的开始日期和结束日期，而且我想知道每天有多少项目在那天出现。

现在，我每天执行一个循环和一个 SQL 查询，但正如您可能已经猜到的那样，那是数千个查询，我希望它可以做得更智能。

如果 2011-04-02 和现在之间的一天什么都不包含，我仍然想要它，但带有 0。

如果可能的话，我希望避免有一个表格，每一天都有一行包含计数。

我希望这是有道理的，我很困在这里。

Answer 1

您应该拥有的是一个表格，其中至少包含从当前表格中的最早日期到当前日期的日期。

然后你可以使用这个表像这样左连接它:

SELECT
dt.date,
COUNT(yt.horse_id)
FROM
dates_table dt
LEFT JOIN your_table yt ON dt.date BETWEEN yt.start_date AND COALESCE(end_date, CURDATE())
GROUP BY dt.date

确保在 COUNT() 函数中有一列 your_table，否则它也会计算 NULL 值。

COALESCE() 函数返回其第一个不是 NULL 的参数，因此如果您没有指定结束日期，则采用当前日期相反。