我有带有用户数据的数据输入表(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/

10-09 02:49