使用SQL统计多列中同一值总出现次数的实现方案

需求为统计所有用户提交内容中各城市的总选择次数：

• users表现有choix1~choix6共6个字段，存储用户选择的城市值
• 原有逻辑仅支持单字段分组计数，无法合并统计6个字段中同一城市的累计出现次数
• 拿到城市总选择次数后，需除以对应城市的可用岗位数量，计算城市受青睐比率

说明：当前城市和岗位数据存储在同一列是CSV导出失败导致，后续会手动录入岗位数量，本次方案暂不处理该部分问题。

原有实现 #

最初单字段统计SQL：

$nRows = $pdo->query('SELECT choix1, count(*) as newchoice from users GROUP BY choix1')->fetchAll(); 

现有冗余PHP实现，对6个字段分别单独查询：

$nRows = $pdo->query('SELECT choix1, count(*) as newchoice from users GROUP BY choix1')->fetchAll(); 
$nRows2 = $pdo->query('SELECT choix2, count(*) as newchoice from users GROUP BY choix2')->fetchAll(); 
$nRows3 = $pdo->query('SELECT choix3, count(*) as newchoice from users GROUP BY choix3')->fetchAll(); 
$nRows4 = $pdo->query('SELECT choix4, count(*) as newchoice from users GROUP BY choix4')->fetchAll(); 
$nRows5 = $pdo->query('SELECT choix5, count(*) as newchoice from users GROUP BY choix5')->fetchAll(); 
$nRows6 = $pdo->query('SELECT choix6, count(*) as newchoice from users GROUP BY choix6')->fetchAll(); 

foreach ($nRows as $nRow) {
    print_r($nRows);
    echo ("<br>");
    $vchoix1=$nRow[1] / 4;
    echo ("<br>");
    echo($vchoix1);
}

相关参考：

• 用户选择列表
• 城市及可用岗位列表
• 数据库结构

用UNION ALL把6个选择字段的有效值纵向合并为单列，再统一分组计数，仅需一次查询即可拿到所有城市的总选择次数，替代原来6次独立查询的冗余逻辑。

优化后SQL #

新增非空判断过滤未填写的无效选项，避免统计脏数据：

SELECT 
    city,
    COUNT(*) AS total_choice
FROM (
    SELECT choix1 AS city FROM users WHERE choix1 IS NOT NULL AND choix1 != ''
    UNION ALL
    SELECT choix2 AS city FROM users WHERE choix2 IS NOT NULL AND choix2 != ''
    UNION ALL
    SELECT choix3 AS city FROM users WHERE choix3 IS NOT NULL AND choix3 != ''
    UNION ALL
    SELECT choix4 AS city FROM users WHERE choix4 IS NOT NULL AND choix4 != ''
    UNION ALL
    SELECT choix5 AS city FROM users WHERE choix5 IS NOT NULL AND choix5 != ''
    UNION ALL
    SELECT choix6 AS city FROM users WHERE choix6 IS NOT NULL AND choix6 != ''
) AS all_choices
GROUP BY city
ORDER BY total_choice DESC;

优化后PHP代码 #

通过PDO::FETCH_KEY_PAIR直接将结果处理为「城市名=>总选择次数」的键值对，简化后续比率计算逻辑：

// 单次查询完成全量统计
$stmt = $pdo->query("
    SELECT 
        city,
        COUNT(*) AS total_choice
    FROM (
        SELECT choix1 AS city FROM users WHERE choix1 IS NOT NULL AND choix1 != ''
        UNION ALL
        SELECT choix2 AS city FROM users WHERE choix2 IS NOT NULL AND choix2 != ''
        UNION ALL
        SELECT choix3 AS city FROM users WHERE choix3 IS NOT NULL AND choix3 != ''
        UNION ALL
        SELECT choix4 AS city FROM users WHERE choix4 IS NOT NULL AND choix4 != ''
        UNION ALL
        SELECT choix5 AS city FROM users WHERE choix5 IS NOT NULL AND choix5 != ''
        UNION ALL
        SELECT choix6 AS city FROM users WHERE choix6 IS NOT NULL AND choix6 != ''
    ) AS all_choices
    GROUP BY city
");
$cityStats = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);

// 后续手动补全各城市岗位数映射即可计算比率
$jobCountMap = [
    // '城市名' => 对应可用岗位数
];
foreach ($cityStats as $city => $totalChoice) {
    $rate = isset($jobCountMap[$city]) ? $totalChoice / $jobCountMap[$city] : 0;
    echo "城市：{$city}，总选择次数：{$totalChoice}，青睐比率：{$rate}<br>";
}

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