SELECT
t1. HOUR HOUR,
COUNT(t2. HOUR) count
FROM
(
SELECT
DATE_FORMAT(
@cdate := DATE_ADD(@cdate, INTERVAL - 1 HOUR),
'%y-%m-%d %H'
) HOUR
FROM
(
SELECT
@cdate := DATE_ADD(
DATE_FORMAT(NOW(), '%y-%m-%d %H'),
INTERVAL + 1 HOUR
)
FROM
city
) t0
LIMIT 24
) t1
LEFT JOIN (
SELECT
DATE_FORMAT(visit_time, '%y-%m-%d %H') HOUR
FROM
visit_log
WHERE
visit_time >= (NOW() - INTERVAL 24 HOUR)
) t2 ON t1. HOUR = t2. HOUR
GROUP BY
t1. HOUR
ORDER BY
t1. HOUR DESC
查询当前时间前24小时日期天数
SELECT
DATE_FORMAT(
@cdate := DATE_ADD(@cdate, INTERVAL - 1 HOUR),
'%y-%m-%d %H'
) HOUR
FROM
(
SELECT
@cdate := DATE_ADD(
DATE_FORMAT(NOW(), '%y-%m-%d %H'),
INTERVAL + 1 HOUR
)
FROM
city #记录大于等于24条的任意一张表
) t0
LIMIT 24
查询前24小时有的数据
SELECT
DATE_FORMAT(visit_time, '%y-%m-%d %H') HOUR
FROM
visit_log #真正要查的记录表
WHERE
visit_time >= (NOW() - INTERVAL 24 HOUR)
最后结果