表中有一个字段有两个状态,想分别统计每个状态下的记录条数usercode isSuc
001 0
001 1
001 1
002 0
002 1sql 语句得到下面的结果
usercode isSuc(0) isSuc(1)
001 1 2
002 1 1
001 0
001 1
001 1
002 0
002 1sql 语句得到下面的结果
usercode isSuc(0) isSuc(1)
001 1 2
002 1 1
select t1.usercode,t1.count1,t2.count2 from
(select t.usercode,count(t.isSuc) count1 from table t where t.isSuc='1'
group by t.usercode) t1,
(select t.usercode,count(t.isSuc) count2 from table t where t.isSuc='0'
group by t.usercode) t2
where t1.usercode = t2.usercode
select count(1),isSuc from Suc_Table group by isSuc
select usercode,sum(case when isSuc=0 then 1 else 0 end) as isSum(0),sum(case when isSuc=1 then 1 else 0 end) as isSum(1) from tbl group by usercode order by usercode;
select usercode,
sum(if(isSuc=0,1,0)) as isSuc(0),
sum(if(isSuc=1,1,0)) as isSuc(1)
from mytable
group by usercode
order by usercode