Mysql按時間段分組查詢來統計會員的個數，mysql個數

Mysql按時間段分組查詢來統計會員的個數，mysql個數

1.使用case when方法（不建議使用）
-

 代碼如下 復制代碼 SELECT
    COUNT(DISTINCT user_id) user_count,
    CASE
    WHEN create_time>1395046800 AND create_time<1395050400 THEN '17:00-18:00'
    WHEN create_time>1395050400 AND create_time<1395054000 THEN '18:00-19:00'
    WHEN create_time>1395054000 AND create_time<1395057600 THEN '19:00-20:00'
    WHEN create_time>1395057600 AND create_time<1395061200 THEN '20:00-21:00'
    ELSE 'unknown'
    END AS `date` www.111cn.net
FROM
    tb_user_online_log
WHERE create_time >1395046800 AND create_time<1395061200
GROUP BY
    `date`
ORDER BY create_time

結果如下

2.使用時間戳轉換後分組

 代碼如下 復制代碼

SELECT
    COUNT(DISTINCT user_id) user_count,
    FROM_UNIXTIME(
        create_time,
        '%Y-%m-%d %H:00:00'
    ) AS hours,
    CONCAT(FROM_UNIXTIME(create_time, '%Y-%m-%d %H:00'),'-',FROM_UNIXTIME(create_time, '%H')+1,":00") AS `date`
FROM
    tb_user_online_log
GROUP BY
    hours
ORDER BY create_time

結果如下 www.111cn.net

注意：case when 效率不高，在數據量大時不推薦使用，這裡僅列出解決方案，僅供參

原文來自:http://www.111cn.net/database/mysql/58986.htm

mysql 分組之後怎統計記錄條數， gourp by 之後的 count

select count(*) from
(SELECT count(*) FROM 表名 WHERE 條件 GROUP BY id ) a ;
 

mysql 時間分組統計

SELECT date_format(FROM_UNIXTIME( `time`),'%Y-%m-%d') AS time,count(*) as count FROM `表名` WHERE 1 group by time