mysql 统计 每天 补齐_mysql 统计一天24小时数据默认补0SQL

mysql 统计一天24小时数据默认补0SQL

select a.click_date,ifnull(b.count,0) as count

from (

SELECT 0 as click_date

union all

SELECT 1 as click_date

union all

SELECT 2 as click_date

union all

SELECT 3 as click_date

union all

SELECT 4 as click_date

union all

SELECT 5 as click_date

union all

SELECT 6 as click_date

union all

SELECT 7 as click_date

union all

SELECT 8 as click_date

union all

SELECT 9 as click_date

union all

SELECT 10 as click_date

union all

SELECT 11 as click_date

union all

SELECT 12 as click_date

union all

SELECT 13 as click_date

union all

SELECT 14 as click_date

union all

SELECT 15 as click_date

union all

SELECT 16 as click_date

union all

SELECT 17 as click_date

union all

SELECT 18 as click_date

union all

SELECT 19 as click_date

union all

SELECT 20 as click_date

union all

SELECT 21 as click_date

union all

SELECT 22 as click_date

union all

SELECT 23 as click_date

) a left join (

SELECT

HOUR (login_time) AS hours,

sum(user_id) AS count

FROM

tb_log_login

WHERE

DATE_FORMAT(login_time, ‘%Y-%m-%d’) = DATE_FORMAT(‘2018-01-18’, ‘%Y-%m-%d’)

GROUP BY

hours

) b on a.click_date = b.hours;