数据格式如下：
id username one two three fore five six
1 u1 2 1 0 0 null 2
2 u2 1 1 1 0 0 0
3 u3 2 1 1 0 2 2
4 u4 2 1 0 0 2 2
查询出不同用户这几个字段中为0的个数，和大于0的个数，结果如下
id username a b
1 u1 2 3 （2个为0，3个大于0）
2 u2 3 3 （3个为0，3个大于0）
3 u3 1 5 （1个为0，5个大于0）
4 u4 2 4 （2个为0，4个大于0）
请教：sql语句该怎么写啊？
------解决思路----------------------
select id,username,one+two+three+four+five+six as a
from (select id,usrename, case when one=0 then 1 else 0 end as one,
case when two=0 then 1 else 0 end as two,
case when three=0 then 1else 0 end as three,
case when four=0 then 1 else 0 end as four,
case when five=0 then 1 else 0 end as five,
case when six=0 then 1 else 0 end as six from tablename
同理算出B列在连接以下。
------解决思路----------------------
上面的sql小調一下
select id,username,
sum(case when qty=0 then 1 else 0 end) as a,
SUM(case when qty>0 then 1 else 0 end) as b
from (
select id,username,one as qty from tablename
union all
select id,username,two from tablename
union all
select id,username,three from tablename
union all
select id,username,fore from tablename
union all
select id,username,five from tablename
union all
select id,username,six from tablename
) as tbl group by id,username order by id