mysql count(*)问题

[桾楉叁杄]

需求是这样的

我对一个表进行查询，其中有两个个字段。

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，该怎么实现

[ACMAIN_CHM]

select 
	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

[rucypli]

select user,report,count(*)
from tb
group by user,report

[ACMAIN_CHM]

或者用你自己的语句，但需要用LEFT JOIN，并且比较复杂，由于你无法预先确定到底是哪一种会没有记录。

[lxq19851204]

select user,sum(IF(report=0,1,0)),sum(IF(report=1,1,0)),sum(IF(report=-1,1,0)) From userbiao

[小小小小周]

[Quote=引用 1 楼 acmain_chm 的回复:]

SQL code
select
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
[/Quote]

用这个方法好,或则用case when..

[ACMAIN_CHM]

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

[wwwwb]

SELECT SUM(IF(report=1,1,0)) AS A ,SUM(IF(report=0,1,0)) AS B,
SUM(IF(report=-1,1,0)) AS C from userbiao group by user