MySQL如何基于漏洞数量与严重级别计算最低风险分并查询最安全团队

实现思路 #

• 第一步：先按团队（address字段）分组，分别统计每个团队的高危（critical）、中危（medium）、低危（low）漏洞的数量
• 第二步：排序优先级按需求设置：
  • 第一优先级：满足critical = 0 AND medium = 0的团队排在最前
  • 第二优先级：中危漏洞数量升序，越少越靠前
  • 第三优先级：低危漏洞数量升序，越少越靠前
  • 兜底优先级：高危漏洞数量升序，覆盖剩余场景的排序逻辑
• 额外说明：你之前写的SQL存在语法错误，WHERE子句必须放在GROUP BY前面才能生效

可直接运行的MySQL语句 #

兼容所有主流MySQL版本的写法：

SELECT 
  address,
  SUM(CASE WHEN severity = 'critical' THEN 1 ELSE 0 END) AS critical_cnt,
  SUM(CASE WHEN severity = 'medium' THEN 1 ELSE 0 END) AS medium_cnt,
  SUM(CASE WHEN severity = 'low' THEN 1 ELSE 0 END) AS low_cnt
FROM scanner
WHERE admin = "xxx" -- 自定义过滤条件放在此处
GROUP BY address
ORDER BY
  -- 第一排序规则：0高危0中危的团队优先展示
  (critical_cnt = 0 AND medium_cnt = 0) DESC,
  -- 第二排序规则：中危漏洞越少越靠前
  medium_cnt ASC,
  -- 第三排序规则：低危漏洞越少越靠前
  low_cnt ASC,
  -- 兜底排序：高危漏洞越少越靠前
  critical_cnt ASC
LIMIT 1000;

如果使用MySQL 8.0及以上版本，可使用更简洁的COUNT_IF函数实现统计：

SELECT 
  address,
  COUNT_IF(severity = 'critical') AS critical_cnt,
  COUNT_IF(severity = 'medium') AS medium_cnt,
  COUNT_IF(severity = 'low') AS low_cnt
FROM scanner
WHERE admin = "xxx"
GROUP BY address
ORDER BY
  (critical_cnt = 0 AND medium_cnt = 0) DESC,
  medium_cnt ASC,
  low_cnt ASC,
  critical_cnt ASC
LIMIT 1000;

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