请教：一条记录中 计算有多少个字段是不为0的

[lwllp2007]

表如下
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的字段

[mysky0103]

写个存储过程

[三省Ta叔]

SELECT sname,
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 ...

[lwllp2007]

能否用sql语句实现？
还有一点，num1 等的字段值比较多

caizhenfang 你的效果不是我需要的

[Coco孙]

select name,
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

[S170393163]

select count(num1) where num1 <> 0

select count(num2) where num1 <> 0

...

[yiseeker]

select sname,
(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