每个日期的 SQL 计数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/808611/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Count for each date
提问by The real napster
I have the following need
我有以下需求
I have a logging table which logs som leads generated each day.
我有一个日志表，记录每天生成的一些潜在客户。
Now I need to pull a report over the amount of leads for each day over the last 10 days.
现在，我需要对过去 10 天内每天的潜在客户数量进行报告。
Lets say the table looks like this:
假设表格如下所示：
tbl_leads
id int,
first_name nvarchar(100),
last_name nvarchar(100),
created_date datetime
And I need to count the number of leads for each day, 10 days total. SO the result set should look something like this:
我需要计算每天的潜在客户数量，总共 10 天。所以结果集应该是这样的：
counted_leads | count_date
5 | 2009-04-30
7 | 2009-04-29
5 | 2009-04-28
7 | 2009-04-27
... and so on
... 等等
Anyone know how to do this the best possible way? My current solution is iterating with a foreach in c# but I would very much like to hand it on the sql server instead in a sp.
任何人都知道如何以最好的方式做到这一点？我当前的解决方案是在 c# 中使用 foreach 进行迭代，但我非常想将它交给 sql server 而不是在 sp 中。
回答by Fabio Vinicius Binder
You can use:
您可以使用：
Select
count(created_date) as counted_leads,
created_date as count_date
from
table
group by
created_date
回答by Dave Swersky
Your created_date field is datetime, so you'll need to strip off the time before the grouping will work if you want to go by date:
您的 created_date 字段是datetime，因此如果您想按日期去，则需要在分组工作之前去除时间：
SELECT COUNT(created_date), created_date
FROM table
WHERE DATEDIFF(created_date, getdate()) < 10
GROUP BY convert(varchar, created_date, 101)
回答by ahains
It is most efficient to do your aggregation by integer and then convert back to datetime for presentation.
按整数进行聚合，然后转换回日期时间进行演示是最有效的。
select
cast(daybucket - 1 as datetime) as count_date,
counted_leads
from
(select
cast(created_date as int) as DayBucket,
count(*) as counted_leads
from mytable
group by cast(created_date as int) ) as countByDay
回答by Niclas Lindgren
When you cast a DateTime to an int it "truncates" at noon, you might want to strip the day out like so
当您将 DateTime 转换为 int 时，它会在中午“截断”，您可能希望像这样去掉一天
cast(DATEADD(DAY, DATEDIFF(DAY, 0, created_date), 0) as int) as DayBucket
回答by user3411804
I had similar question however mine involved a column Convert(date,mydatetime). I had to alter the best answer as follows:
我有类似的问题，但我的问题涉及一列 Convert(date,mydatetime)。我不得不改变最佳答案如下：
Select
count(created_date) as counted_leads,
Convert(date,created_date) as count_date
from table
group by Convert(date,created_date)
回答by Suvabrata Roy
CREATE PROCEDURE [dbo].[sp_Myforeach_Date]
-- Add the parameters for the stored procedure here
@SatrtDate as DateTime,
@EndDate as dateTime,
@DatePart as varchar(2),
@OutPutFormat as int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
Declare @DateList Table
(Date varchar(50))
WHILE @SatrtDate<= @EndDate
BEGIN
INSERT @DateList (Date) values(Convert(varchar,@SatrtDate,@OutPutFormat))
IF Upper(@DatePart)='DD'
SET @SatrtDate= DateAdd(dd,1,@SatrtDate)
IF Upper(@DatePart)='MM'
SET @SatrtDate= DateAdd(mm,1,@SatrtDate)
IF Upper(@DatePart)='YY'
SET @SatrtDate= DateAdd(yy,1,@SatrtDate)
END
SELECT * FROM @DateList
END
Just put this Code and call the SP in This way
只需放置此代码并以这种方式调用SP
exec sp_Myforeach_Date @SatrtDate='03 Jan 2010',@EndDate='03 Mar 2010',@DatePart='dd',@OutPutFormat=106
回答by northpole
Select count(created_date) total
, created_dt
from table
group by created_date
order by created_date desc
