表如下
ID sname num1 num2 num3 num4 num5 1 aa1 1 2 0 5 2
2 aa2 1 0 6 0 2
3 aa3 0 4 3 0 2比如统计
aa1 num3=0 则有一个为0的字段
aa2 num2=0,num4=0 则有两个为0的字段
ID sname num1 num2 num3 num4 num5 1 aa1 1 2 0 5 2
2 aa2 1 0 6 0 2
3 aa3 0 4 3 0 2比如统计
aa1 num3=0 则有一个为0的字段
aa2 num2=0,num4=0 则有两个为0的字段
SUM(CASE num1 WHEN num1>0 THEN 1 END) AS num1,
SUM(CASE num2 WHEN num2>0 THEN 1 END) AS num2,
SUM(CASE num3 WHEN num3>0 THEN 1 END) AS num3,
SUM(CASE num4 WHEN num4>0 THEN 1 END) AS num4,
SUM(CASE num5 WHEN num5>0 THEN 1 END) AS num5
FROM T1
GROUP BY sname ...
还有一点,num1 等的字段值比较多caizhenfang 你的效果不是我需要的
case num1 when 0 then 1 else 0 end as a +
case num2 when 0 then 1 else 0 end as b +
case num3 when 0 then 1 else 0 end as c +
case num4 when 0 then 1 else 0 end as d as 等0的个数
from tb1
select count(num1) where num1 <> 0select count(num2) where num1 <> 0...
(case num1 when 0 then 1 else 0 end ) +
(case num2 when 0 then 1 else 0 end ) +
(case num3 when 0 then 1 else 0 end ) +
...
(case numN when 0 then 1 else 0 end ) as 零的个数from t1