在同一SQL查询中同时统计分组数量与占比的实现问题

我来帮你分析下问题所在，然后给出更高效的解决方案：

问题根源 #

你的原始查询通过UNION将5个独立统计的结果拆成5行，每行只有一个非零计数列、其他列都是0，再用MAX()聚合合并成一行。当你尝试加入总数量计算占比时，问题出在：每个子查询行里只有对应列有有效值，其他列都是0，直接在行内计算占比的话，只有对应行的占比是正确的，其他行的占比会是0，最终聚合后虽然能拿到正确的计数，但占比计算会出错；另外这种多次UNION的写法会重复扫描表5次，效率极低。

最优解决方案：用CASE WHEN一次扫描完成统计 #

我们可以用SUM(CASE ...)的方式，在一次表扫描中完成所有分组计数，同时关联总数量计算占比，逻辑更清晰，性能也大幅提升。

完整查询代码

SELECT
  -- 基础统计数值
  counthypo,
  -- 计算占比，*1.0 避免整数除法截断问题
  ROUND((counthypo * 1.0 / total) * 100, 2) AS percHypo,
  counteuthyroid,
  ROUND((counteuthyroid * 1.0 / total) * 100, 2) AS percEuthyroid,
  counthyper,
  ROUND((counthyper * 1.0 / total) * 100, 2) AS percHyper,
  none,
  ROUND((none * 1.0 / total) * 100, 2) AS percNone,
  unknown,
  ROUND((unknown * 1.0 / total) * 100, 2) AS percUnknown
FROM (
  SELECT 
    -- 统计甲减分组数量
    SUM(CASE WHEN `recheck_t4` < `recheck_t4_range_low` THEN 1 ELSE 0 END) AS counthypo,
    -- 统计甲功正常分组数量
    SUM(CASE WHEN `recheck_t4` BETWEEN `recheck_t4_range_low` AND `recheck_t4_range_high` THEN 1 ELSE 0 END) AS counteuthyroid,
    -- 统计甲亢分组数量
    SUM(CASE WHEN `recheck_t4` > `recheck_t4_range_high` THEN 1 ELSE 0 END) AS counthyper,
    -- 统计none分组数量
    SUM(CASE WHEN `nordvmfollowup` = '1' AND `isotope` = '1' THEN 1 ELSE 0 END) AS none,
    -- 统计unknown分组数量
    SUM(CASE WHEN `recheck_t4` IS NULL AND `isotope` = '1' AND `nordvmfollowup` IS NULL THEN 1 ELSE 0 END) AS unknown,
    -- 总记录数
    total
  FROM tbl_I131_data
  -- 交叉连接获取全表总数量
  CROSS JOIN (SELECT COUNT(*) AS total FROM tbl_I131_data) AS total_count
) AS stats;

关键细节说明

1. 避免整数除法：像MySQL这类数据库默认整数相除会截断为整数（比如5/100会得到0），通过*1.0将整数转换为浮点数，确保得到正确的小数占比。
2. 性能提升：原查询需要扫描表5次，新写法只扫描1次，数据量越大，性能差距越明显。
3. 可维护性：所有分组条件集中在CASE WHEN中，逻辑直观，后续修改或新增分组都很方便。

如果你坚持保留原UNION结构 #

如果一定要基于原始查询的逻辑修改，可以先将所有统计值聚合为一行，再关联总数量计算占比，解决每行只有一个非零值的问题：

WITH aggregated_stats AS (
  SELECT 
    MAX(counthypo) AS counthypo,
    MAX(counteuthyroid) AS counteuthyroid,
    MAX(counthyper) AS counthyper,
    MAX(none) AS none,
    MAX(unknown) AS unknown
  FROM ( 
    SELECT count(*) as counthypo, 0 as counteuthyroid, 0 as counthyper, 0 as none, 0 as unknown FROM tbl_I131_data WHERE `recheck_t4` < `recheck_t4_range_low` 
    UNION 
    SELECT 0 as counthypo, count(*) as counteuthyroid, 0 as counthyper, 0 as none, 0 as unknown FROM tbl_I131_data WHERE (`recheck_t4` BETWEEN `recheck_t4_range_low` AND `recheck_t4_range_high`) 
    UNION 
    SELECT 0 as counthypo, 0 as counteuthyroid, count(*) as counthyper, 0 as none, 0 as unknown FROM tbl_I131_data WHERE `recheck_t4` > `recheck_t4_range_high` 
    UNION 
    SELECT 0 as counthypo, 0 as counteuthyroid, 0 as counthyper, count(*) as none, 0 as unknown FROM tbl_I131_data WHERE `nordvmfollowup` = '1' AND `isotope` = '1' 
    UNION 
    SELECT 0 as counthypo, 0 as counteuthyroid, 0 as counthyper, 0 as none, count(*) as unknown FROM tbl_I131_data WHERE `recheck_t4` is null AND `isotope` = '1' and `nordvmfollowup` is null 
  ) AS i
)
SELECT
  counthypo,
  ROUND((counthypo * 1.0 / total) * 100, 2) AS percHypo,
  counteuthyroid,
  ROUND((counteuthyroid * 1.0 / total) * 100, 2) AS percEuthyroid,
  counthyper,
  ROUND((counthyper * 1.0 / total) * 100, 2) AS percHyper,
  none,
  ROUND((none * 1.0 / total) * 100, 2) AS percNone,
  unknown,
  ROUND((unknown * 1.0 / total) * 100, 2) AS percUnknown
FROM aggregated_stats
CROSS JOIN (SELECT COUNT(*) AS total FROM tbl_I131_data) AS total_count;

不过这种写法的性能仍然远不如CASE WHEN的方案，仅适合需要兼容旧逻辑的场景。

内容的提问来源于stack exchange，提问作者Daniël Cronk