MySQL使用CASE WHEN统计多值严重级别数量问题求助

咱们先拆解下你当前SQL里的问题：

你的CASE WHEN条件判断逻辑完全错了：

• 写level= '1,2,3'是把level字段和字符串'1,2,3'做相等匹配，这显然不是你想要的——你实际想判断level是否属于1、2、3中的任意一个
• 后来尝试的level= '1' and '2' and '3'逻辑也不对，这种写法等价于(level='1') AND TRUE AND TRUE，只会统计level=1的记录，完全不符合需求

正确的SQL写法 #

根据你的期望输出，这里有两种靠谱的写法，都能得到你要的结果：

方法1：使用IN匹配多个值

select client, dateadded,
count(case when level IN (1,2,3) then 1 end) as Moderate,
count(case when level IN (4,5,6,7) then 1 end) as Severe,
count(case when level IN (8,9,10) then 1 end) as Critical
from table1 
where client = 'abc' 
group by client, dateadded

方法2：使用BETWEEN匹配连续范围

因为你的级别是连续的数值区间，用BETWEEN会更简洁：

select client, dateadded,
count(case when level BETWEEN 1 AND 3 then 1 end) as Moderate,
count(case when level BETWEEN 4 AND 7 then 1 end) as Severe,
count(case when level BETWEEN 8 AND 10 then 1 end) as Critical
from table1 
where client = 'abc' 
group by client, dateadded

额外小技巧：用SUM替代COUNT #

你也可以用SUM来统计数量，逻辑是满足条件返回1，否则返回0，效果和COUNT一致：

select client, dateadded,
sum(case when level IN (1,2,3) then 1 else 0 end) as Moderate,
sum(case when level IN (4,5,6,7) then 1 else 0 end) as Severe,
sum(case when level IN (8,9,10) then 1 else 0 end) as Critical
from table1 
where client = 'abc' 
group by client, dateadded

验证结果 #

执行上面的SQL后，会得到符合预期的输出（注：你给出的期望输出中2019-02-16的Critical为2，但根据提供的表数据，当天只有1条level=9的记录属于Critical，可能是数据笔误，但SQL逻辑完全正确）：

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