select `date`, sum(if(stat='comp',1,0)) as compCount, sum(if(stat='drop',1,0)) as dropCount from work_tb group by `date`
if(stat='comp',1,0) 也可以换成你的 case stat when 'comp' then 1 else 0 end或者看上去简单一点儿的。select `date`, sum(stat='comp') as compCount, sum(stat='drop') as dropCount from work_tb group by `date`
select R.date,comp,drop from (select distinct(date) from moon ) as R left outer join (select date,count(date) as comp from moon where stat='comp' group by date) as A on R.date=A.date left outer join (select date,count(date) as drop from moon where stat='drop' group by date) as B on R.date=B.date
sum(if(stat='comp',1,0)) as compCount,
sum(if(stat='drop',1,0)) as dropCount
from work_tb
group by `date`
case stat
when 'comp' then 1 else 0 end或者看上去简单一点儿的。select `date`,
sum(stat='comp') as compCount,
sum(stat='drop') as dropCount
from work_tb
group by `date`
from
(select distinct(date) from moon ) as R
left outer join
(select date,count(date) as comp from moon where stat='comp' group by date) as A
on R.date=A.date
left outer join
(select date,count(date) as drop from moon where stat='drop' group by date) as B
on R.date=B.date