wwwwww

SELECT DATE_FORMAT(date,'%Y-%m-%d') AS date,IFNULL(data.hour,0) AS hour, IFNULL(data.num, 0) AS count
FROM (
    SELECT @days := DATE_ADD(@days, INTERVAL - 1 DAY) AS date
    FROM (SELECT @days := DATE_ADD(CURDATE(), INTERVAL + 1 DAY)
    FROM bus_access_record
    ) day
    WHERE DATE_FORMAT(@days, '%Y-%m-%d') >=
    DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 29 DAY), '%Y-%m-%d')
    ORDER BY date
    ) dates
    LEFT JOIN (
    SELECT  HOUR (create_time)+1  AS hour, COUNT(1) AS num, DATE(create_time) AS time
    FROM bus_access_record
    WHERE
    DATE_FORMAT(create_time, '%Y-%m-%d %H:%i:%S') >=
    DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 30 DAY), '%Y-%m-%d %H:%i:%S')
    GROUP BY DATE(create_time),hour
    ) data ON DATE(time) = date
ORDER BY date desc,hour;

select a.lockdate,ifnull(b.count,0) as count
from (
SELECT 1 as lockdate
union all
SELECT 2 as lockdate
union all
SELECT 3 as lockdate
union all
SELECT 4 as lockdate
union all
SELECT 5 as lockdate
union all
SELECT 6 as lockdate
union all
SELECT 7 as lockdate
union all
SELECT 8 as lockdate
union all
SELECT 9 as lockdate
union all
SELECT 10 as lockdate
union all
SELECT 11 as lockdate
union all
SELECT 12 as lockdate
union all
SELECT 13 as lockdate
union all
SELECT 14 as lockdate
union all
SELECT 15 as lockdate
union all
SELECT 16 as lockdate
union all
SELECT 17 as lockdate
union all
SELECT 18 as lockdate
union all
SELECT 19 as lockdate
union all
SELECT 20 as lockdate
union all
SELECT 21 as lockdate
union all
SELECT 22 as lockdate
union all
SELECT 23 as lockdate
union all
SELECT 24 as lockdate
) a left join (
SELECT
HOUR (create_time)+1 AS hours,
count(id) as count
FROM
bus_access_record
WHERE
DATE_FORMAT(create_time, '%Y-%m-%d') in ('2023-01-13',"2023-01-28","2023-01-29","2023-01-30","2023-01-31","2023-02-01","2023-02-02","2023-02-03")

GROUP BY
hours
) b on a.lockdate = b.hours;