問題描述
我使用的是 SQL Server 2008.
I am using SQL Server 2008.
我想編寫一個(gè)查詢，提供給定天數(shù)的總活動(dòng)量.具體來說，我想統(tǒng)計(jì)過去 7 天每天的總票數(shù).
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
我需要我的結(jié)果看起來像這樣
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
此查詢不起作用，因?yàn)樗鼉H計(jì)算(幾乎完全相同)同時(shí)發(fā)生的投票.當(dāng)然，我只想看特定的一天.我該如何實(shí)現(xiàn)?
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 列是一個(gè) datetime，但您只需要其中的 date 部分.最簡單的方法是將其轉(zhuǎn)換為 date 類型.您可以在此處閱讀有關(guān) 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.
這篇關(guān)于如何查詢以獲取過去 7 天的總數(shù)?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！