Mysql报表统计常用sql

原创
2018/01/16 08:45
阅读数 3.8K
  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

     

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部