select a.name,count(*) from a inner join b on a.m=b.m where b.cp>0 group by a.name
select a.name, count(1) as cpcount from a join b on a.m=b.m and b.cp>0 group by a.name
假如 再多一列, 要求也统计 =0的数列呢?就是要求得到的结果是 name >0 =0 a 3 2 b 2 1 c 2 2
select group by a.name, sum(if(b.cp>0,1,0)), sum(if(b.cp=0,1,0)) from a inner join b on a.m=b.m group by a.name 建议提问时一次说清楚。问题说明越详细,回答也会越准确!参见如何提问。(提问的智慧)
select a.name, sum(case when b.cp>0 then 1 else 0 end) as `>0`, sum(case when b.cp=0 then 1 else 0 end) as '=0' from a join b on a.m=b.m group by a.name
select a.name, sum(case when b.cp>0 then 1 else 0 end) as `>0`, sum(case when b.cp=0 then 1 else 0 end) as `=0` from a join b on a.m=b.m group by a.name
from a inner join b on a.m=b.m
where b.cp>0
group by a.name
a.name,
count(1) as cpcount
from a
join b on a.m=b.m and b.cp>0
group by a.name
name >0 =0
a 3 2
b 2 1
c 2 2
select group by a.name,
sum(if(b.cp>0,1,0)),
sum(if(b.cp=0,1,0))
from a inner join b on a.m=b.m
group by a.name 建议提问时一次说清楚。问题说明越详细,回答也会越准确!参见如何提问。(提问的智慧)
a.name,
sum(case when b.cp>0 then 1 else 0 end) as `>0`,
sum(case when b.cp=0 then 1 else 0 end) as '=0'
from a
join b on a.m=b.m
group by a.name
a.name,
sum(case when b.cp>0 then 1 else 0 end) as `>0`,
sum(case when b.cp=0 then 1 else 0 end) as `=0`
from a
join b on a.m=b.m
group by a.name