分类站长必备下的文章

MySql查询一天注册了多少人然后是精确到每小时

时间：2022-06-25
分类：html资源
阅读：次

select sum(user_registered) as"注册时间" ,count(*) as "注册人数" from wp_users group by date_format(user_registered, '%Y-%m-%d %H ')='2017-7-25'

精确到每小时

SELECT CURDATE() - INTERVAL 1 DAY AS '昨天',
IFNULL(SUM(CASE HOUR(FROM_UNIXTIME(user_registered,'%Y-%M-%D %H:%I:%S')) WHEN 0 THEN 1 ELSE 0 END),0) AS '0',
IFNULL(SUM(CASE HOUR(FROM_UNIXTIME(user_registered,'%Y-%M-%D %H:%I:%S')) WHEN 1 THEN 1 ELSE 0 END),0) AS '1',
IFNULL(SUM(CASE HOUR(FROM_UNIXTIME(user_registered,'%Y-%M-%D %H:%I:%S')) WHEN 2 THEN 1 ELSE 0 END),0) AS '2',
IFNULL(SUM(CASE HOUR(FROM_UNIXTIME(user_registered,'%Y-%M-%D %H:%I:%S')) WHEN 3 THEN 1 ELSE 0 END),0) AS '3',
IFNULL(SUM(CASE HOUR(FROM_UNIXTIME(user_registered,'%Y-%M-%D %H:%I:%S')) WHEN 4 THEN 1 ELSE 0 END),0) AS '4',
IFNULL(SUM(CASE HOUR(FROM_UNIXTIME(user_registered,'%Y-%M-%D %H:%I:%S')) WHEN 5 THEN 1 ELSE 0 END),0) AS '5',
IFNULL(SUM(CASE HOUR(FROM_UNIXTIME(user_registered,'%Y-%M-%D %H:%I:%S')) WHEN 6 THEN 1 ELSE 0 END),0) AS '6',
IFNULL(SUM(CASE HOUR(FROM_UNIXTIME(user_registered,'%Y-%M-%D %H:%I:%S')) WHEN 7 THEN 1 ELSE 0 END),0) AS '7',
IFNULL(SUM(CASE HOUR(FROM_UNIXTIME(user_registered,'%Y-%M-%D %H:%I:%S')) WHEN 8 THEN 1 ELSE 0 END),0) AS '8',
IFNULL(SUM(CASE HOUR(FROM_UNIXTIME(user_registered,'%Y-%M-%D %H:%I:%S')) WHEN 9 THEN 1 ELSE 0 END),0) AS '9',
IFNULL(SUM(CASE HOUR(FROM_UNIXTIME(user_registered,'%Y-%M-%D %H:%I:%S')) WHEN 10 THEN 1 ELSE 0 END),0) AS '10',
IFNULL(SUM(CASE HOUR(FROM_UNIXTIME(user_registered,'%Y-%M-%D %H:%I:%S')) WHEN 11 THEN 1 ELSE 0 END),0) AS '11',
IFNULL(SUM(CASE HOUR(FROM_UNIXTIME(user_registered,'%Y-%M-%D %H:%I:%S')) WHEN 12 THEN 1 ELSE 0 END),0) AS '12',
IFNULL(SUM(CASE HOUR(FROM_UNIXTIME(user_registered,'%Y-%M-%D %H:%I:%S')) WHEN 13 THEN 1 ELSE 0 END),0) AS '13',
IFNULL(SUM(CASE HOUR(FROM_UNIXTIME(user_registered,'%Y-%M-%D %H:%I:%S')) WHEN 14 THEN 1 ELSE 0 END),0) AS '14',
IFNULL(SUM(CASE HOUR(FROM_UNIXTIME(user_registered,'%Y-%M-%D %H:%I:%S')) WHEN 15 THEN 1 ELSE 0 END),0) AS '15',
IFNULL(SUM(CASE HOUR(FROM_UNIXTIME(user_registered,'%Y-%M-%D %H:%I:%S')) WHEN 16 THEN 1 ELSE 0 END),0) AS '16',
IFNULL(SUM(CASE HOUR(FROM_UNIXTIME(user_registered,'%Y-%M-%D %H:%I:%S')) WHEN 17 THEN 1 ELSE 0 END),0) AS '17',
IFNULL(SUM(CASE HOUR(FROM_UNIXTIME(user_registered,'%Y-%M-%D %H:%I:%S')) WHEN 18 THEN 1 ELSE 0 END),0) AS '18',
IFNULL(SUM(CASE HOUR(FROM_UNIXTIME(user_registered,'%Y-%M-%D %H:%I:%S')) WHEN 19 THEN 1 ELSE 0 END),0) AS '19',
IFNULL(SUM(CASE HOUR(FROM_UNIXTIME(user_registered,'%Y-%M-%D %H:%I:%S')) WHEN 20 THEN 1 ELSE 0 END),0) AS '20',
IFNULL(SUM(CASE HOUR(FROM_UNIXTIME(user_registered,'%Y-%M-%D %H:%I:%S')) WHEN 21 THEN 1 ELSE 0 END),0) AS '21',
IFNULL(SUM(CASE HOUR(FROM_UNIXTIME(user_registered,'%Y-%M-%D %H:%I:%S')) WHEN 22 THEN 1 ELSE 0 END),0) AS '22',
IFNULL(SUM(CASE HOUR(FROM_UNIXTIME(user_registered,'%Y-%M-%D %H:%I:%S')) WHEN 23 THEN 1 ELSE 0 END),0) AS '23'
FROM wp_users
WHERE user_registered >= UNIX_TIMESTAMP(CURDATE() - INTERVAL 1 DAY)
AND user_registered < UNIX_TIMESTAMP(CURDATE())