SQL查询按天分组

Question

我想列出所有销售额，并按天对总和进行分组。

Sales (saleID INT, amount INT, created DATETIME)

注意:我使用的是 SQL Server 2005。

Answer 1

如果您使用的是 SQL Server，

dateadd(DAY,0, datediff(day,0,created)) 将返回创建的日期

例如，如果销售是在“2009-11-02 06:12:55.000”创建的，dateadd(DAY,0, datediff(day,0, 创建)) return '2009-11-02 00:00:00.000'

select sum(amount) as total, dateadd(DAY,0, datediff(day,0, created)) as created
from sales
group by dateadd(DAY,0, datediff(day,0, created))