如何实现先按Make列分组再按Model列前缀分组的车辆数据统计？

我有一个本地汽车数据库，需要在页面表格里实现层级化数据展示：

1. 初始展示各**品牌（Make）**对应的车辆总数
2. 点击某个品牌后，展示该品牌下按车型（Model）开头字符串分组的车辆数量

数据库表结构如下：

当前使用的错误PHP代码：

$mysql = "SELECT make, model FROM cars car_make WHERE make = (SELECT model FROM cars WHERE make like = cars_make.make) GROUP BY make, model";
$myresult = mysqli_query($connect, $mysql);
echo var_dump($myresult);

1. 第一步：获取各品牌车辆总数 #

先执行SQL统计每个品牌的总车辆数，再用PHP渲染成可点击的表格：

SQL语句

SELECT make, COUNT(*) AS total_cars 
FROM cars 
GROUP BY make 
ORDER BY make;

PHP代码示例

// 假设$connect已完成数据库连接初始化
$brandSql = "SELECT make, COUNT(*) AS total_cars FROM cars GROUP BY make ORDER BY make";
$brandResult = mysqli_query($connect, $brandSql);

// 渲染品牌统计表格
echo "<table border='1'>";
echo "<tr><th>品牌</th><th>车辆总数</th></tr>";
while ($row = mysqli_fetch_assoc($brandResult)) {
    // 点击品牌时传递参数到当前页面
    echo "<tr>
            <td><a href='?make=" . urlencode($row['make']) . "'>" . htmlspecialchars($row['make']) . "</a></td>
            <td>" . $row['total_cars'] . "</td>
          </tr>";
}
echo "</table>";

2. 第二步：点击品牌后展示车型分组统计 #

当页面接收到make参数时，执行SQL提取该品牌下的车型前缀并统计数量：

SQL语句（提取Model空格前的前缀，如Brezza ZDI取Brezza）

SELECT 
    SUBSTRING_INDEX(model, ' ', 1) AS model_prefix,
    COUNT(*) AS model_count
FROM cars 
WHERE make = ?
GROUP BY model_prefix 
ORDER BY model_prefix;

PHP代码示例

// 检查是否有品牌参数传入
if (isset($_GET['make']) && !empty($_GET['make'])) {
    $selectedMake = $_GET['make'];
    
    // 使用预处理语句防止SQL注入
    $modelSql = "SELECT SUBSTRING_INDEX(model, ' ', 1) AS model_prefix, COUNT(*) AS model_count FROM cars WHERE make = ? GROUP BY model_prefix ORDER BY model_prefix";
    $stmt = mysqli_prepare($connect, $modelSql);
    mysqli_stmt_bind_param($stmt, "s", $selectedMake);
    mysqli_stmt_execute($stmt);
    $modelResult = mysqli_stmt_get_result($stmt);
    
    // 渲染车型分组统计表格
    echo "<h3>" . htmlspecialchars($selectedMake) . " 车型分组统计</h3>";
    echo "<table border='1'>";
    echo "<tr><th>车型前缀</th><th>车辆数量</th></tr>";
    while ($row = mysqli_fetch_assoc($modelResult)) {
        echo "<tr>
                <td>" . htmlspecialchars($row['model_prefix']) . "</td>
                <td>" . $row['model_count'] . "</td>
              </tr>";
    }
    echo "</table>";
    
    // 返回品牌列表的链接
    echo "<p><a href='?'>返回品牌列表</a></p>";
}

关键说明 #

• 原SQL存在语法错误（like =格式错误、子查询逻辑混乱），上述方案重新梳理了业务逻辑
• 使用预处理语句绑定参数，避免SQL注入风险
• 车型前缀提取逻辑可根据实际需求调整，比如车型前缀规则不是空格分隔，可修改SUBSTRING_INDEX的分隔符参数

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