按天分组的Sql查询

I want to list all sales, and group the sum by day.

我想把所有的销售额都列出来，然后按天计算。

Sales (saleID INT, amount INT, created DATETIME)

Update I am using SQL Server 2005

更新我正在使用SQL Server 2005

8 个解决方案

#1

122

if you're using SQL Server,

如果你在使用SQL Server，

dateadd(DAY,0, datediff(day,0, created)) will return the day created

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

for example, if the sale created on '2009-11-02 06:12:55.000', dateadd(DAY,0, datediff(day,0, created)) return '2009-11-02 00:00:00.000'

例如，如果在“2009-11-02 06:12 . 57 000”上创建的销售，dateadd(DAY,0, datediff, DAY,0, create)返回“2009-11-02 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))

#2

For SQL Server:

SQL服务器:

GROUP BY datepart(year,datefield), 
    datepart(month,datefield), 
    datepart(day,datefield)

or faster (from Q8-Coder):

或更快(从Q8-Coder):

GROUP BY dateadd(DAY,0, datediff(day,0, created))

For MySQL:

MySQL:

GROUP BY year(datefield), month(datefield), day(datefield)

or better (from Jon Bright):

或者更好(Jon Bright):

GROUP BY date(datefield)

For Oracle:

Oracle:

GROUP BY to_char(datefield, 'yyyy-mm-dd')

or faster (from IronGoofy):

或更快(从IronGoofy):

GROUP BY trunc(created);

For Informix (by Jonathan Leffler):

用于Informix (Jonathan Leffler):

GROUP BY date_column
GROUP BY EXTEND(datetime_column, YEAR TO DAY)

#3

If you're using MySQL:

如果使用的是MySQL:

SELECT
    DATE(created) AS saledate,
    SUM(amount)
FROM
    Sales
GROUP BY
    saledate

If you're using MS SQL 2008:

如果您正在使用MS SQL 2008:

SELECT
    CAST(created AS date) AS saledate,
    SUM(amount)
FROM
    Sales
GROUP BY
    CAST(created AS date)

#4

If you're using SQL Server, you could add three calculated fields to your table:

如果您正在使用SQL Server，您可以向您的表中添加三个计算字段:

Sales (saleID INT, amount INT, created DATETIME)

ALTER TABLE dbo.Sales
  ADD SaleYear AS YEAR(Created) PERSISTED
ALTER TABLE dbo.Sales
  ADD SaleMonth AS MONTH(Created) PERSISTED
ALTER TABLE dbo.Sales
  ADD SaleDay AS DAY(Created) PERSISTED

and now you could easily group by, order by etc. by day, month or year of the sale:

现在你可以很容易地按日、月或年进行分组、按等订购:

SELECT SaleDay, SUM(Amount)
FROM dbo.Sales
GROUP BY SaleDay

Those calculated fields will always be kept up to date (when your "Created" date changes), they're part of your table, they can be used just like regular fields, and can even be indexed (if they're "PERSISTED") - great feature that's totally underused, IMHO.

这些计算出来的字段将始终保持最新(当您的“创建”日期更改时)，它们是您的表的一部分，它们可以像常规字段一样使用，甚至可以被编入索引(如果它们是“持久化的”)——这是一个完全未被充分利用的特性，IMHO。

Marc

马克

#5

actually this depends on what DBMS you are using but in regular SQL convert(varchar,DateColumn,101) will change the DATETIME format to date (one day)

实际上，这取决于您使用的是什么DBMS，但是在常规的SQL转换(varchar,DateColumn,101)中，将会改变DATETIME格式到日期(一天)

so:

所以:

SELECT 
    sum(amount) 
FROM 
    sales 
GROUP BY 
    convert(varchar,created,101)

the magix number 101 is what date format it is converted to

治安官编号101是它被转换成的日期格式

#6

For oracle you can

对于oracle可以

group by trunc(created);

as this truncates the created datetime to the previous midnight.

因为这会将创建的datetime截断到前一个午夜。

Another option is to

另一个选择是

group by to_char(created, 'DD.MM.YYYY');

which achieves the same result, but may be slower as it requires a type conversion.

实现了相同的结果，但是可能会比较慢，因为它需要类型转换。

#7

For PostgreSQL:

PostgreSQL的:

GROUP BY to_char(timestampfield, 'yyyy-mm-dd')

or using cast:

或者使用演员:

GROUP BY timestampfield::date

if you want speed, use the second option and add an index:

如果你想要速度，使用第二个选项并添加一个索引:

CREATE INDEX tablename_timestampfield_date_idx ON  tablename(date(timestampfield));

#8

-1

use linq

使用linq

from c in Customers
group c by DbFunctions.TruncateTime(c.CreateTime) into date
orderby date.Key descending
select new  
{
    Value = date.Count().ToString(),
    Name = date.Key.ToString().Substring(0, 10)
}

#1

122