需求是这样的我对一个表进行查询,其中有两个个字段。user有多种结果。report有三种结果0、1、-1。我想对三个分别计数。我想的sql语句是
select * from (select user,count(*) from userbiao where report=1 group by user) as a,
(select user,count(*) from userbiao where report=0 group by user) as b,
(select user,count(*) from userbiao where report=-1 group by user) as c但是问题出现了,就是当我report这个字段没有等于-1的时候,什么都不显示了,我想让如果没有等于-1的情况下,这个user后面跟的就是0,该怎么实现
select * from (select user,count(*) from userbiao where report=1 group by user) as a,
(select user,count(*) from userbiao where report=0 group by user) as b,
(select user,count(*) from userbiao where report=-1 group by user) as c但是问题出现了,就是当我report这个字段没有等于-1的时候,什么都不显示了,我想让如果没有等于-1的情况下,这个user后面跟的就是0,该怎么实现
Sum(if(report=1,1,0)) as U1,
sum(If(report=0,1,0)) as U0,
sum(if(report=-1,1,0)) As U_1
from userbiao
from tb
group by user,report
用这个方法好,或则用case when..
Sum(if(report=1,1,0)) as U1,
sum(If(report=0,1,0)) as U0,
sum(if(report=-1,1,0)) As U_1
from userbiao
group by user
SUM(IF(report=-1,1,0)) AS C from userbiao group by user