SQL 按天分组，带计数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1455054/
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 group by day, with count
提问by Chris McCall
I've got a log table in SQL Server that looks like this:
我在 SQL Server 中有一个日志表，如下所示：
CREATE TABLE [dbo].[RefundProcessLog](
[LogId] [bigint] IDENTITY(1,1) NOT NULL,
[LogDate] [datetime] NOT NULL,
[LogType] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[RefundId] [int] NULL,
[RefundTypeId] [smallint] NULL,
[LogMessage] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LoggedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_RefundProcessLog] PRIMARY KEY CLUSTERED
(
[LogId] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
What I want is a list of results that represents how many different refundids were processed each day, throwing out any NULLs.
我想要的是一个结果列表，它表示每天处理多少个不同的退款 ID，丢弃任何 NULL。
What SQL would I need to write to produce these results?
我需要编写什么 SQL 才能产生这些结果？
回答by scottm
I like this approach in (MS SQL):
我喜欢 (MS SQL) 中的这种方法：
SELECT
Convert(char(8), LogDate, 112),
count(distinct RefundId)
FROM RefundProcessing
GROUP BY Convert(char(8), LogDate, 112)
回答by Steve Kass
select cast(LogDate as date) as LogDate, count(refundId) as refundCount
from yourTable
group by cast(LogDate as date)
Depending on the dialect of SQL you're using, you may have to change the CAST to something else. The expression should convert the LogDate to a date-only value.
根据您使用的 SQL 方言，您可能需要将 CAST 更改为其他内容。该表达式应将 LogDate 转换为仅限日期的值。
Also, if you say "different refundId" because there could be repeated values of refundId that you only want to count once, use count(DISTINCT refundId)
另外，如果您说“不同的refundId”是因为您只想计算一次refundId 的重复值，请使用count(DISTINCTrefundId)
回答by Charles Bretana
What database vendor are you using? Whichever it is, replace the "DateOnly(LogDate)" in the following with the appropriate construict to extract the date portion (strip off the time) from the logdate column value and then try this:
您使用的是哪个数据库供应商？无论是哪一个，用适当的构造替换下面的“DateOnly(LogDate)”，以从 logdate 列值中提取日期部分（去掉时间），然后试试这个：
Select [DateOnly(LogDate)], Count Distinct RefundId
From RefundProcessLog
Group By [DateOnly(LogDate)]
In Sql server, for e.g., the appropriate construct would be:
例如，在 Sql 服务器中，适当的构造是：
Select DateAdd(day, 0, DateDiff(day, 0, LogDate)), Count(Distinct RefundId)
From RefundProcessLog
Group By DateAdd(day, 0, DateDiff(day, 0, LogDate))
回答by pbz
SELECT COUNT(RefundId), DateOnly(LogDate) LoggingDate
FROM RefundProcessLog
GROUP BY DateOnly(LogDate)
"DateOnly" is specific to your SQL database, which you haven't specified.
“DateOnly”特定于您尚未指定的 SQL 数据库。
For SQL Server you could use DateAdd(dd,0, DateDiff(dd,0,LogDate)) for "DateOnly"
对于 SQL Server，您可以将 DateAdd(dd,0, DateDiff(dd,0,LogDate)) 用于“DateOnly”
回答by ahsteele
SQL Server 2008 introduced the datedatatype which makes the following possible:
SQL Server 2008 引入的date数据类型使以下成为可能：
select convert(date, LogDate),
,count(refundid) AS 'refunds'
from RefundProcessing
group by convert(date,LogDate)
order by convert(date,LogDate)
回答by Gratzy
Select count(*), LogDate, refundid from RefundProcessLog
where refundid is not null
group by LogDate, refundid
Edit:
编辑：
Or drop RefundID if you don't want it broken down by refunds
或者如果您不希望它被退款分解，请删除 RefundID
回答by Michael Todd
In SqlServer, it would be something like:
在 SqlServer 中，它将类似于：
select datepart(YEAR, [LogDate]), datepart(MONTH, [LogDate]), datepart(DAY, [LogDate]), count(refundid) as [Count]
from [RefundProcessing]
group by datepart(YEAR, [LogDate]), datepart(MONTH, [LogDate]), datepart(DAY, [LogDate])
