select COACH_NAME,sum(case when STATE=0 then 1 else 0 end) as '0',
sum(case when STATE = 1 then 1 else 0 end) as '1' ,
SUM(case when STATE = 9 then 1 ELSE 0 END) as '9'
from tt_f_schedule WHERE tt_f_schedule.START_TIME >= '2016-09-01 00:00:00' AND
tt_f_schedule.END_TIME <= '2016-09-30 24:00:00'
group by COACH_NAME
sum(case when STATE = 1 then 1 else 0 end) as '1' ,
SUM(case when STATE = 9 then 1 ELSE 0 END) as '9'
from tt_f_schedule WHERE tt_f_schedule.START_TIME >= '2016-09-01 00:00:00' AND
tt_f_schedule.END_TIME <= '2016-09-30 24:00:00'
group by COACH_NAME
查出所有教练状态分别为0,1,9的数据量
结果为
select COACH_NAME,count(COACH_NAME) as num from tt_f_schedule where STATE = 1 group by COACH_NAME;
分别查询不同教练 state=1的数据