1. Use case when method (not recommended)
-
The code is as follows |
Copy Code |
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 |
The results are as follows
2. Post-conversion grouping using timestamps
The code is as follows |
Copy Code |
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, ": xx") as ' date ' From Tb_user_online_log GROUP by Hours ORDER by Create_time
|
The results are as follows Www.111cn.net
Note: When the case is inefficient, it is not recommended when the data is large, only the solution is listed here, only the
Originally from: http://www.111cn.net/database/mysql/58986.htm
MySQL counts the number of members by grouping queries by time period