如何在SQL中按病毒感染频次对查询分组并统计出现次数

你的核心问题是把频次判断和计数拆成了多个独立字段，这样反而复杂化了逻辑。我们应该先计算每个用户的总感染次数，再对次数进行分组映射，最后统计各组的人数。

步骤拆解 #

1. 计算每个用户的感染次数：按用户分组，统计其符合指定病毒列表的感染记录数（假设yes表示感染，你可以根据实际字段值调整）。
2. 映射感染次数到频次分组：将0次映射为F0，1次→F1，2次→F2，3次→F3，4次→F4，5次及以上→F5+。
3. 统计各频次分组的人数：对映射后的分组进行计数。

修正后的完整SQL代码 #

-- 外层查询：对感染次数进行分组映射，并统计每组人数
SELECT
  CASE
    WHEN infection_count = 0 THEN 'F0'
    WHEN infection_count = 1 THEN 'F1'
    WHEN infection_count = 2 THEN 'F2'
    WHEN infection_count = 3 THEN 'F3'
    WHEN infection_count = 4 THEN 'F4'
    WHEN infection_count >= 5 THEN 'F5+'
  END AS Virus_Frequency,
  COUNT(*) AS VIRUS_COUNT
FROM (
  -- 子查询：计算每个用户的总感染次数
  SELECT
    name,
    SUM(
      CASE
        -- 当illness属于指定病毒列表，且为感染状态（假设yes表示感染）时记1分
        WHEN illness IN (43598435, 24343, 45413, 2455, 54574511, 2074123, 34351153) 
             AND `yes/no` = 'yes' THEN 1
        -- 未感染或不符合病毒列表的情况记0分
        ELSE 0
      END
    ) AS infection_count
  FROM your_table_name -- 替换成你的实际表名
  GROUP BY name
) AS user_infection_counts
-- 如果不需要F0分组，可以加上WHERE infection_count > 0
GROUP BY Virus_Frequency
-- 按频次分组排序，让结果更整齐
ORDER BY 
  CASE Virus_Frequency
    WHEN 'F0' THEN 0
    WHEN 'F1' THEN 1
    WHEN 'F2' THEN 2
    WHEN 'F3' THEN 3
    WHEN 'F4' THEN 4
    WHEN 'F5+' THEN 5
  END;

关键说明 #

• 子查询逻辑：通过SUM(CASE...)累加每个用户的有效感染次数，GROUP BY name确保每个用户只计算一次总次数。
• 分组映射：外层的CASE语句清晰地将感染次数映射到你需要的频次标签，避免了原代码中多字段判断的混乱。
• 排序优化：最后的ORDER BY子句让结果按F0到F5+的顺序排列，符合常规的频次统计展示逻辑。
• 字段调整：如果你的yes/no字段实际值不是'yes'（比如用1/0表示），记得修改CASE里的判断条件。

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