blocks|key|1203171|text|如果您正在使用SQL+Server，|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1203172|dateadd(DAY,0,+datediff(day,0,+created))将返回创建的日期|offset|length|style|CODE|1203173|例如，如果销售创建于'2009-11-02+06:12:55.000'，则dateadd(DAY,0,+datediff(day,0,+created))返回'2009-11-02+00:00:00.000‘|1203174|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))|code-block|syntax|javascript|1203175|entityMap^0|0|0|14|0|11|14|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@$D|S|E|T|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|U|8|@$D|V|E|W|F|G]]|9|@]|A|$]]|$1|J|3|K|5|L|7|X|8|@]|9|@]|A|$M|N]]|$1|O|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|P|$]]

if you're using SQL Server,

<code>dateadd(DAY,0, datediff(day,0, created))</code> will return the day created

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

<pre><code>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))
</code></pre>

blocks|key|1203300|text|对于SQL+Server：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1203301|GROUP+BY+datepart(year,datefield),+
++++datepart(month,datefield),+
++++datepart(day,datefield)|code-block|syntax|javascript|1203302|或更快(来自Q8-Coder)：|1203303|GROUP+BY+dateadd(DAY,0,+datediff(day,0,+created))|1203304|对于MySQL：|1203305|GROUP+BY+year(datefield),+month(datefield),+day(datefield)|1203306|或者更好(来自Jon+Bright)：|1203307|GROUP+BY+date(datefield)|1203308|对于Oracle：|1203309|GROUP+BY+to_char(datefield,+'yyyy-mm-dd')|1203310|或更快(来自IronGoofy)：|1203311|GROUP+BY+trunc(created);|1203312|For+Informix+(+Jonathan+Leffler撰写)：|1203313|GROUP+BY+date_column
GROUP+BY+EXTEND(datetime_column,+YEAR+TO+DAY)|1203314|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|16|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|17|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|18|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|19|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|1A|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|1B|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|1C|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|1D|8|@]|9|@]|A|$E|F]]|$1|S|3|T|5|6|7|1E|8|@]|9|@]|A|$]]|$1|U|3|V|5|D|7|1F|8|@]|9|@]|A|$E|F]]|$1|W|3|X|5|6|7|1G|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|D|7|1H|8|@]|9|@]|A|$E|F]]|$1|10|3|11|5|6|7|1I|8|@]|9|@]|A|$]]|$1|12|3|13|5|D|7|1J|8|@]|9|@]|A|$E|F]]|$1|14|3|-4|5|6|7|1K|8|@]|9|@]|A|$]]]|15|$]]

For SQL Server:

<pre><code>GROUP BY datepart(year,datefield), 
 datepart(month,datefield), 
 datepart(day,datefield)
</code></pre>

or faster (from Q8-Coder):

<pre><code>GROUP BY dateadd(DAY,0, datediff(day,0, created))
</code></pre>

For MySQL:

<pre><code>GROUP BY year(datefield), month(datefield), day(datefield)
</code></pre>

or better (from Jon Bright):

<pre><code>GROUP BY date(datefield)
</code></pre>

For Oracle:

<pre><code>GROUP BY to_char(datefield, 'yyyy-mm-dd')
</code></pre>

or faster (from IronGoofy):

<pre><code>GROUP BY trunc(created);
</code></pre>

For Informix (by Jonathan Leffler):

<pre><code>GROUP BY date_column
GROUP BY EXTEND(datetime_column, YEAR TO DAY)
</code></pre>

blocks|key|1418402|text|如果您使用的是MySQL：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1418403|SELECT
++++DATE(created)+AS+saledate,
++++SUM(amount)
FROM
++++Sales
GROUP+BY
++++saledate|code-block|syntax|javascript|1418404|如果您使用的是MS+SQL+2008：|1418405|SELECT
++++CAST(created+AS+date)+AS+saledate,
++++SUM(amount)
FROM
++++Sales
GROUP+BY
++++CAST(created+AS+date)|1418406|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

If you're using MySQL:

<pre><code>SELECT
 DATE(created) AS saledate,
 SUM(amount)
FROM
 Sales
GROUP BY
 saledate
</code></pre>

If you're using MS SQL 2008:

<pre><code>SELECT
 CAST(created AS date) AS saledate,
 SUM(amount)
FROM
 Sales
GROUP BY
 CAST(created AS date)
</code></pre>

blocks|key|1203350|text|实际上，这取决于您所使用的DBMS，但在常规SQL中，convert(varchar,DateColumn,101)会将日期时间格式更改为日期(一天)。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1203351|所以：|1203352|SELECT+
++++sum(amount)+
FROM+
++++sales+
GROUP+BY+
++++convert(varchar,created,101)|code-block|syntax|javascript|1203353|magix数字101是将其转换为的日期格式|1203354|entityMap^0|R|V|0|0|0|7|3|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|T|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|U|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|V|8|@$9|W|A|X|B|C]]|D|@]|E|$]]|$1|O|3|-4|5|6|7|Y|8|@]|D|@]|E|$]]]|P|$]]

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

so:

<pre><code>SELECT 
 sum(amount) 
FROM 
 sales 
GROUP BY 
 convert(varchar,created,101)
</code></pre>

the magix number <code>101</code> is what date format it is converted to

blocks|key|1418596|text|如果您使用的是SQL+Server，则可以向表中添加三个计算字段：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1418597|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|code-block|syntax|javascript|1418598|现在你可以很容易地分组，按销售的日期、月份或年份等排序：|1418599|SELECT+SaleDay,+SUM(Amount)
FROM+dbo.Sales
GROUP+BY+SaleDay|1418600|这些计算字段将始终保持最新(当您的“创建”日期更改时)，它们是表的一部分，它们可以像常规字段一样使用，甚至可以被索引(如果它们是“持久化”的)-这是一个完全未得到充分利用的伟大功能，IMHO。|1418601|Marc|1418602|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

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

<pre><code>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
</code></pre>

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

<pre><code>SELECT SaleDay, SUM(Amount)
FROM dbo.Sales
GROUP BY SaleDay
</code></pre>

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.

Marc

blocks|key|1203448|text|对于PostgreSQL：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1203449|GROUP+BY+to_char(timestampfield,+'yyyy-mm-dd')|code-block|syntax|javascript|1203450|或者使用cast：|1203451|GROUP+BY+timestampfield::date|1203452|如果您想要速度，请使用第二个选项并添加索引：|1203453|CREATE+INDEX+tablename_timestampfield_date_idx+ON++tablename(date(timestampfield));|1203454|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

For PostgreSQL:

<pre><code>GROUP BY to_char(timestampfield, 'yyyy-mm-dd')
</code></pre>

or using cast:

<pre><code>GROUP BY timestampfield::date
</code></pre>

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

<pre><code>CREATE INDEX tablename_timestampfield_date_idx ON tablename(date(timestampfield));
</code></pre>

blocks|key|1203243|text|对于oracle，您可以|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1203244|group+by+trunc(created);|code-block|syntax|javascript|1203245|因为这会将创建的datetime截断到前一个午夜。|1203246|另一种选择是|1203247|group+by+to_char(created,+'DD.MM.YYYY');|1203248|它实现了相同的结果，但可能会更慢，因为它需要类型转换。|1203249|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|U|8|@]|9|@]|A|$E|F]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

For oracle you can

<pre><code>group by trunc(created);
</code></pre>

as this truncates the created datetime to the previous midnight.

Another option is to 

<pre><code>group by to_char(created, 'DD.MM.YYYY');
</code></pre>

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

blocks|key|1203470|text|使用linq|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1203471|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)
}|code-block|syntax|javascript|1203472|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

use linq

<pre><code>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)
}
</code></pre>

I want to list all sales, and group the sum by day.
<pre><code>Sales (saleID INT, amount INT, created DATETIME)
</code></pre>
NOTE: I am using SQL Server 2005.

SQL query to group by day

我想列出所有的销售额，并按天对总数进行分组。Sales (saleID INT, amount INT, created DATETIME)注意:我使用的是SQL Server 2005。

问要按天分组的SQL查询
EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问要按天分组的SQL查询EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问要按天分组的SQL查询
EN