按半小时报表统计。 SELECT COUNT(1) AS value, DATE_FORMAT(dataStartTime,'%H:%i') AS category
FROM (
SELECT DATE_FORMAT(CONCAT(DATE(phone_start_date),' ', HOUR(phone_start_date),':', FLOOR(MINUTE(phone_start_date)/30)*30),'%Y-%m-%d %H:%i') AS dataStartTime
FROM phone_history
WHERE DATE_FORMAT(phone_start_date, '%Y-%m-%d') = DATE_FORMAT('2018-01-12', '%Y-%m-%d') ) a
GROUP BY category
ORDER BY category
按天报表统计 select date_format( phone_start_date, '%Y-%m-%d') as category, count(1) as total,
sum(case phone_status when 0 then 1 else 0 end) as state0,
sum(case phone_status when 1 then 1 else 0 end) as state1,
sum(case phone_status when 2 then 1 else 0 end) as state2,
sum(case phone_status when 3 then 1 else 0 end) as state3,
sum(ifnull(datetime,0)) AS totalDateTime,
count(distinct user_no) AS userCount
from phone_history
group by category
order by category