这就是我如何应对的
SQL Server
,因此需要一些工作,但这只是一个开始。
with cteBase as
(
select n from (values(0),(0)) as t(n)
)
select top 7 dates_table.day_all_week,count(u.user_id) as USERS_COUNT
from
(
select dateadd(day, ROW_NUMBER() over (order by c.n)-1,
cast(DATEADD(DD, 1 - DATEPART(DW, GETDATE()), GETDATE()) as date)) as day_all_week
from ctebase as c,ctebase as c2,cteBase as c3
) as dates_table
left join #users as u on dates_table.day_all_week=u.last_accessed
group by dates_table.day_all_week
order by dates_table.day_all_week
Top 7有点粗糙,你可以使用where来选择所需的日期,目前设置为从一周的第一天开始
(cast(DATEADD(DD, 1 - DATEPART(DW, GETDATE()), GETDATE()) as date))
.这可以改成固定的日期。
你可以简化工作日的日期。保留理货表意味着你可以很容易地扩展到月/年等。
select dateadd(DD,n,(cast(DATEADD(DD, 1 - DATEPART(DW, GETDATE()), GETDATE()) as date)))
from (values(0),(1),(2),(3),(4),(5),(6)) as t(n)
