我有带有用户数据的数据输入表(servicedata),列是userid、billdate(datetime类型)等。
我需要每周(本周)获取用户数。
查询
select BillDate
,DATENAME(dw,BillDate) as day
,count(BillDate) as total
from ServiceData
group by
BillDate,DATENAME(dw,BillDate)
order by BillDate desc
这只是获取billdate的日期,但我想获取星期日条目的计数,星期一条目的计数…从星期天到星期六(本周)这可能吗?
预期产量
ID | TOTAL | DAY
--------------------------
1 | 23 | Sun
2 | 54 | Mon
3 | 17 | Tues
4 | 56 | Thus
5 | 45 | Fri
6 | 78 | Sat
最佳答案
这应该可以做到:
SELECT
row_number() over (order by (SELECT 1)) ID,
count(*) Total,
LEFT(Datename(weekday, Cast(Billdate as date)), 3) Day
FROM
ServiceData
WHERE
BillDate >= dateadd(week, datediff(d, -1, getdate()-2)/7, -1)
GROUP BY
Cast(Billdate as date)
ORDER BY
Cast(Billdate as date)
关于sql - 如何在SQL Server中获取本周的每日数据,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/52381935/