php - MySQL 统计并显示某个范围内的所有日期

Question

我的 MySQL 查询出现问题。我有下表:

[id]
[from] DATE (Y-m-d)
[days] INT
[...]

我有两个 PHP 变量 $start 和 $end。例如，我想要这样的结果:

$start = 2012-19-03
$end = 2012-24-03
$result = array(
[0] => COUNT of rows WHERE '2012-19-03' BETWEEN from AND from + INTERVAL days DAY
[1] => COUNT of rows WHERE '2012-20-03' BETWEEN from AND from + INTERVAL days DAY
[2] => COUNT of rows WHERE '2012-21-03' BETWEEN from AND from + INTERVAL days DAY 
[4] => COUNT of rows WHERE '2012-22-03' BETWEEN from AND from + INTERVAL days DAY
[5] => COUNT of rows WHERE '2012-23-03' BETWEEN from AND from + INTERVAL days DAY 
[6] => COUNT of rows WHERE '2012-24-03' BETWEEN from AND from + INTERVAL days DAY 
}

到目前为止，我都是在 while 循环中执行此操作，并每天查询一次，但应该有更好的方法。我的问题是GROUP BY，其中数据中不存在的日期。

更详细的示例:

示例数据:

id, from,       days
1,  2012-15-03, 2
2,  2012-15-03, 5
3,  2012-13-03, 20
4,  2012-16-03, 1

期望的结果:

$start = 2012-11-03
$end = 2012-19-03
$result = array(
[0] => 0 (ROWS IN DB WHERE '2012-11-03' BETWEEN from AND from+days -- no match in data),
[1] => 0 (ROWS IN DB WHERE '2012-12-03' BETWEEN from AND from+days -- no match in data),
[2] => 1 (ROWS IN DB WHERE '2012-13-03' BETWEEN from AND from+days -- id #3 matches),
[3] => 1 (and so on),
[4] => 3,
[5] => 4,
[6] => 3,
[7] => 2
)

像这样..

我已经好几个小时没有找到办法了，希望你能帮助我，问候

Answer 1

试试这个:

SELECT `from`, COUNT(*) TotalRows
FROM tableName
WHERE `from` <= date('$start') AND `from` >= date('$end')
GROUP BY `from`
ORDER BY `from`