MySQL中按星期名称对Datetime日期分组统计的实现方案

需求说明 #

数据库test表的start字段存储了大量YYYY-MM-DD HH:MM:SS格式的datetime数据，需要按英文星期名称（如Monday、Tuesday）分组统计记录数量，最终通过Google Charts展示统计结果。

1. 修正SQL查询语句 #

原查询按start字段分组会导致每条时间单独统计，无法实现按星期分组。需要使用MySQL的DAYNAME()函数提取星期名称，再按该名称分组统计：

$sql_8 = "SELECT DAYNAME(`start`) AS day_name, COUNT(*) AS number FROM test GROUP BY day_name ORDER BY WEEKDAY(`start`)";

• DAYNAME(start)：提取start日期对应的英文星期名称（如Monday）
• COUNT(*)：统计对应星期的记录总数
• GROUP BY day_name：按星期名称分组聚合
• ORDER BY WEEKDAY(start)：按周一到周日的顺序排序结果（WEEKDAY返回0=周一，6=周日），避免图表展示顺序混乱

2. 调整PHP与前端代码 #

原代码直接输出start字段值，现在需要改为读取分组后的星期名称和统计数，同时优化JS数据格式避免语法错误：

PHP查询处理 #

<?php   
    $result_8 = mysqli_query($conn, $sql_8);
    // 处理SQL查询错误（可选）
    if (!$result_8) {
        die(mysqli_error($conn));
    }
    $chartData = [];
    while($row = mysqli_fetch_assoc($result_8)) { 
        // 将数据整理为Google Charts所需的格式
        $chartData[] = [$row["day_name"], (int)$row["number"]];
    }
?>

Google Charts前端代码 #

<script type="text/javascript">
    google.charts.load('current', {'packages':['table']});
    google.charts.setOnLoadCallback(drawTable);
    function drawTable() {
        var data = new google.visualization.DataTable();
        data.addColumn('string', 'Day');
        data.addColumn('number', 'Count');
        // 直接通过json_encode输出格式化后的数组，避免JS语法错误
        data.addRows(<?php echo json_encode($chartData); ?>); 
                 
        var table = new google.visualization.Table(document.getElementById('table_div_8'));
        table.draw(data, {showRowNumber: true, width: '100%', height: '100%'});
    }
</script>

内容的提问来源于stack exchange，提问作者evavienna