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())

最后更新于 2022-06-25 03:47:38 并被添加「」标签，已有 1081 位网友阅读过。

本站使用「署名 4.0 国际」创作共享协议，可自由转载、引用，但需署名作者且注明文章出处