如何在MySQL查询或PHP中计算COUNT(CASE WHEN)生成字段的总和？

一、在MySQL中直接计算总和（推荐） #

这种方式更高效，数据库端计算可减少数据传输量，同时避免PHP端处理结果集的逻辑错误。

优化后的SQL逻辑 #

你所有location1~location7的CASE判断都包含icon_type = '{$icon}'，可将该条件移到WHERE子句简化表达式；同时直接在SELECT中计算总和，甚至可以用更高效的方式直接统计符合条件的总行数：

SELECT 
  teacher_type, 
  link_url, 
  link_title,
  COUNT(CASE WHEN row_value = 1 AND link_column = 1 THEN 1 END) AS location1,
  COUNT(CASE WHEN row_value = 1 AND link_column = 2 THEN 1 END) AS location2,
  COUNT(CASE WHEN row_value = 1 AND link_column = 3 THEN 1 END) AS location3,
  COUNT(CASE WHEN row_value = 1 AND link_column = 4 THEN 1 END) AS location4,
  COUNT(CASE WHEN row_value = 2 AND link_column = 1 THEN 1 END) AS location5,
  COUNT(CASE WHEN row_value = 2 AND link_column = 2 THEN 1 END) AS location6,
  COUNT(CASE WHEN row_value = 2 AND link_column = 3 THEN 1 END) AS location7,
  COUNT(*) AS total_clicks -- 直接统计符合WHERE条件的总行数，即7个location的总和
FROM ww_click_tracking 
WHERE 
  click_date BETWEEN {$date_clause}
  AND teacher_type = {$value}
  AND icon_type = '{$icon}'
  -- 过滤仅属于7个location的记录，避免统计无关数据
  AND (
    (row_value = 1 AND link_column IN (1,2,3,4)) 
    OR (row_value = 2 AND link_column IN (1,2,3))
  )

对应PHP代码 #

注意：原代码直接操作$result对象是错误的，mysqli_query返回的是结果集对象，必须先获取行数据才能访问字段：

foreach ($iconArray as $icon) {
    $query = "
        SELECT 
          teacher_type, 
          link_url, 
          link_title,
          COUNT(CASE WHEN row_value = 1 AND link_column = 1 THEN 1 END) AS location1,
          COUNT(CASE WHEN row_value = 1 AND link_column = 2 THEN 1 END) AS location2,
          COUNT(CASE WHEN row_value = 1 AND link_column = 3 THEN 1 END) AS location3,
          COUNT(CASE WHEN row_value = 1 AND link_column = 4 THEN 1 END) AS location4,
          COUNT(CASE WHEN row_value = 2 AND link_column = 1 THEN 1 END) AS location5,
          COUNT(CASE WHEN row_value = 2 AND link_column = 2 THEN 1 END) AS location6,
          COUNT(CASE WHEN row_value = 2 AND link_column = 3 THEN 1 END) AS location7,
          COUNT(*) AS total_clicks
        FROM ww_click_tracking 
        WHERE 
          click_date BETWEEN {$date_clause}
          AND teacher_type = {$value}
          AND icon_type = '{$icon}'
          AND (
            (row_value = 1 AND link_column IN (1,2,3,4)) 
            OR (row_value = 2 AND link_column IN (1,2,3))
          )
    ";

    $result = mysqli_query($link, $query);
    if (!$result) {
        // 处理查询错误，比如日志记录
        continue;
    }

    // 获取聚合查询的单行结果
    $row = mysqli_fetch_assoc($result);
    if (!$row) {
        continue;
    }

    // 直接判断总和是否大于0
    if ($row['total_clicks'] > 0) {
        // 处理业务逻辑，比如使用$row['location1']~$row['location7']的统计数据
    }
}

二、PHP端计算总和 #

如果无法修改SQL，也可在PHP中处理，但需保证正确获取结果集数据：

foreach ($iconArray as $icon) {
    $query = "
        SELECT teacher_type, link_url, link_title,
          COUNT(CASE WHEN icon_type = '{$icon}' AND row_value = 1 AND link_column = 1 THEN 1 END) AS location1,
          COUNT(CASE WHEN icon_type = '{$icon}' AND row_value = 1 AND link_column = 2 THEN 1 END) AS location2,
          COUNT(CASE WHEN icon_type = '{$icon}' AND row_value = 1 AND link_column = 3 THEN 1 END) AS location3,
          COUNT(CASE WHEN icon_type = '{$icon}' AND row_value = 1 AND link_column = 4 THEN 1 END) AS location4,
          COUNT(CASE WHEN icon_type = '{$icon}' AND row_value = 2 AND link_column = 1 THEN 1 END) AS location5,
          COUNT(CASE WHEN icon_type = '{$icon}' AND row_value = 2 AND link_column = 2 THEN 1 END) AS location6,
          COUNT(CASE WHEN icon_type = '{$icon}' AND row_value = 2 AND link_column = 3 THEN 1 END) AS location7
        FROM ww_click_tracking 
        WHERE click_date BETWEEN {$date_clause}
        AND teacher_type = {$value}";

    $result = mysqli_query($link, $query);
    if (!$result) {
        continue;
    }

    $row = mysqli_fetch_assoc($result);
    if (!$row) {
        continue;
    }

    $locationTotal = 0;
    for ($i=1; $i <=7; $i++) {
        $locationTotal += (int)$row['location'.$i]; // 强制转整数，避免非数值类型问题
    }

    if ($locationTotal > 0) {
        // 处理业务逻辑
    }
}

关键优化点 #

1. SQL注入防护：原代码直接拼接变量到SQL中存在严重安全风险，建议使用mysqli预处理语句：

foreach ($iconArray as $icon) {
    // 拆分date_clause为起始和结束日期（假设原date_clause格式为'2024-01-01' AND '2024-01-31'）
    list($start_date, $end_date) = explode(' AND ', $date_clause);
    $start_date = trim($start_date, "'");
    $end_date = trim($end_date, "'");

    $sql = "
        SELECT 
          teacher_type, 
          link_url, 
          link_title,
          COUNT(CASE WHEN row_value = 1 AND link_column = 1 THEN 1 END) AS location1,
          COUNT(CASE WHEN row_value = 1 AND link_column = 2 THEN 1 END) AS location2,
          COUNT(CASE WHEN row_value = 1 AND link_column = 3 THEN 1 END) AS location3,
          COUNT(CASE WHEN row_value = 1 AND link_column = 4 THEN 1 END) AS location4,
          COUNT(CASE WHEN row_value = 2 AND link_column = 1 THEN 1 END) AS location5,
          COUNT(CASE WHEN row_value = 2 AND link_column = 2 THEN 1 END) AS location6,
          COUNT(CASE WHEN row_value = 2 AND link_column = 3 THEN 1 END) AS location7,
          COUNT(*) AS total_clicks
        FROM ww_click_tracking 
        WHERE 
          click_date BETWEEN ? AND ?
          AND teacher_type = ?
          AND icon_type = ?
          AND (
            (row_value = 1 AND link_column IN (1,2,3,4)) 
            OR (row_value = 2 AND link_column IN (1,2,3))
          )
    ";

    $stmt = mysqli_prepare($link, $sql);
    mysqli_stmt_bind_param($stmt, "ssis", $start_date, $end_date, $value, $icon);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);

    $row = mysqli_fetch_assoc($result);
    if ($row && $row['total_clicks'] > 0) {
        // 处理业务逻辑
    }

    mysqli_stmt_close($stmt);
}

2. 简化SQL逻辑：将公共条件icon_type = '{$icon}'移到WHERE子句，减少CASE表达式重复代码，提升可读性和性能。
3. 结果集处理正确性：原代码直接操作$result对象是错误的，必须通过mysqli_fetch_assoc等方法获取行数据后才能访问字段。

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