按半小时报表统计。
SELECT COUNT(1) AS value, DATE_FORMAT(dataStartTime,'%H:%i') AS categoryFROM (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 dataStartTimeFROM phone_historyWHERE DATE_FORMAT(phone_start_date, '%Y-%m-%d') = DATE_FORMAT('-01-12', '%Y-%m-%d') ) aGROUP BY categoryORDER 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 userCountfrom phone_historygroup by categoryorder by category
