mysql - 如何创建按天数分组的连续日期图表？

Question

我想创建一个在 x 轴上从最小日期到最大日期连续日期的图表；并每天计数。

我有这张表：

ID  Date      

==============

1   2018-01-05    

2   2018-01-05

3   2018-01-07

4   2018-01-08

5   2018-01-08

这就是我所拥有的，但我无法获得连续的日期

$sql = "SELECT date,COUNT(*) FROM table GROUP BY date";

$result = $conn->query($sql);

while ($row = $result->fetch_assoc()) {

    $x_axis[] = $row['date'];

    $values[] = $row['COUNT(*)'];

}

所需结果的示例是（当该中间日期没有记录时，我想得到零）：

2018-01-05 => 2

2018-01-06 => 0

2018-01-07 => 1

2018-01-08 => 2

Answer 1

像这样做：$finaldata = array();$sql = "SELECT date, COUNT(*) total FROM ( SELECT * FROM Table ORDER BY date ASC) AS sub GROUP BY date";$result = $conn->query($sql);while ($row = $result->fetch_assoc()) {&nbsp; &nbsp; $x_axis[] = $row['date'];&nbsp; &nbsp; $values[] = $row['COUNT(*)'];}for($date = $x_axis[0]; strtotime($date) <= strtotime(end($x_axis)); ) {&nbsp; &nbsp; $key = array_search($date, $x_axis);&nbsp; &nbsp; $finaldata[$date] = $key === FALSE ? 0 : $values[$key];&nbsp; &nbsp; $date = date('Y-m-d', strtotime('+1 day', strtotime($date)))}echo "<pre>";print_r($finaldata);die;您的最终数据在$finaldata数组中