Mysql报表统计常用sql

1. 按半小时报表统计。

   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

    

2. 按天报表统计

   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

    

转载于:https://my.oschina.net/u/217147/blog/1607601