如何查询以获取过去 7 天的总数?

问题描述

我使用的是 SQL Server 2008.

I am using SQL Server 2008.

我想编写一个查询，提供给定天数的总活动量.具体来说，我想统计过去 7 天每天的总票数.

I want to write a query that gives me total activity for a number of given days. Specifically, I want to count total votes per day for the last seven days.

我的桌子是这样的:

 VoteID --- VoteDate --------------  Vote --- BikeID

 1          2012-01-01 08:24:25      1        1234 
 2          2012-01-01 08:24:25      0        5678
 3          2012-01-02 08:24:25      1        1289
 4          2012-01-03 08:24:25      0        1234
 5          2012-01-04 08:24:25      1        5645
 6          2012-01-05 08:24:25      0        1213
 7          2012-01-06 08:24:25      1        1234
 8          2012-01-07 08:24:25      0        1125

我需要我的结果看起来像这样

I need my results to look like this

VoteDate ---- Total
2012-01-01    5
2012-01-02    6
2012-01-03    7
2012-01-04    1
2012-01-05    3

我的想法是我必须做这样的事情:

My thought is that I have to do something like this:

SELECT    SUM(CASE WHEN Vote = 1 THEN 1 ELSE 0 END) AS Total
FROM      Votes
GROUP BY  VoteDate

此查询不起作用，因为它仅计算(几乎完全相同)同时发生的投票.当然，我只想看特定的一天.我该如何实现?

This query doesn't work because it counts only votes that occurred (almost exactly) at the same time. Of course, I want to look only at a specific day. How do I make this happen?

推荐答案

Cast it as a date:

Cast it as a date:

SELECT    
     cast(VoteDate as date) as VoteDate, 
     SUM(CASE WHEN Vote = 1 THEN 1 ELSE 0 END) AS Total
FROM      Votes
WHERE VoteDate between dateadd(day, -7, GETDATE()) and GETDATE()
GROUP BY  cast(VoteDate as date)

您的 VoteDate 列是一个 datetime，但您只需要其中的 date 部分.最简单的方法是将其转换为 date 类型.您可以在此处阅读有关 SQL Server 日期类型的更多信息.

Your VoteDate column is a datetime, but you just want the date part of it. The easiest way to do that is to cast it as a date type. You can read more about SQL Server date types here.

如果你的 Vote 列是 1 或 0，你可以只做 sum(vote) as Total 而不是做 case声明.

And if your Vote column is either 1 or 0, you can just do sum(vote) as Total instead of doing the case statement.

这篇关于如何查询以获取过去 7 天的总数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！