select DATE_FORMAT(t2.date, ‘%m-%d’) as time, count(s.user_id) as count from ws_sign_in s right join
(select a.Date date from (select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c ) a
where a.Date between (select DATE_SUB(CURDATE(),INTERVAL 30 DAY)) and CURDATE()) as t2 on SUBSTRING(s.data_create_time,1,10) = t2.date
group by t2.date
sql语句统计最近30天内的人数,没有数据显示为0
最新推荐文章于 2024-05-01 22:54:01 发布
这篇博客探讨了一个SQL查询,用于从ws_sign_ins表中联合多个子查询,以获取过去30天每天的日期格式及对应的签到用户数。通过GROUP BY子句按日期进行分组并使用COUNT函数计算每天的签到计数。
摘要由CSDN通过智能技术生成