如何使用SQL [duplicate]按时间分组

Possible Duplicate:
SQL Query Group By Datetime problem?

可能重复:SQL查询组由Datetime问题吗?

I am working on an application with 2 steps.

我正在处理一个有两个步骤的应用程序。

Scan logs and persist data from them in a database.
扫描日志并将数据保存在数据库中。
Read data from database and visualize the data.
从数据库读取数据并可视化数据。

The first step is more or less finished. I try to explain the background and my reguirement with the second step.

第一步差不多完成了。我尝试用第二步来解释背景和我的要求。

Each row in the database consists of some info like logdate, logfilename, LogType, logMessage etc. So I want for example write SQL that summarize a given LogType per day.

数据库中的每一行都包含一些信息，比如logdate、logfilename、LogType、logMessage等等。

This is the columns:

这是一列:

[LogDate] [datetime] NOT NULL,
[Computer] [varchar](50) NOT NULL,
[Type] [varchar](50) NOT NULL,
[FileName] [varchar](100) NOT NULL,
[LineNo] [int] NOT NULL,
[UserName] [varchar](50) NOT NULL,
[Message] [varchar](max) NOT NULL,

I imagine the output could be like this if I want to show all rows with Type=TDBError:

我想象输出可能是这样的如果我想显示所有的行类型=TDBError:

Date        Sum
2012-10-01  3
2012-10-02  12
2012-10-03  40
2012-10-05  24
2012-10-06  18

So at date 2012-10-01 there was 3 rows in DB where Type=TDBError. At date 2012-10-02 there was 12 etc.

在日期2012-10-01时，DB中有3行Type=TDBError。在2012-10-02年有12个。

How should I write the SQL for this ?

我应该如何为它编写SQL ?

4 个解决方案

#1

Assuming SQL Server 2008 or newer:

假设SQL Server 2008或更新:

SELECT 
  [Date] = CONVERT(DATE, LogDate), 
  [Sum] = COUNT(*)
FROM dbo.Log_Table_Name
WHERE [Type] = 'DBError'
GROUP BY CONVERT(DATE, LogDate)
ORDER BY [Date];

#2

GROUP BY DATEPART(day, date), DATEPART(month, date), DATEPART(year, date)

按日期部分(日、日)、日期部分(月、日)、日期部分(年、日)分组

#3

You can do a group by the parts of the time

你可以按时间来分组

GROUP BY date(log_date), month(log_date), day(log_date)

#4

Select Cast(FLOOR(CAST(DATE as float)) as DateTime) as Date,COUNT(*) as [SUM]
from Log_Table_Name
Group by Cast(FLOOR(CAST(DATE as float)) as DateTime)
order by Cast(FLOOR(CAST(DATE as float)) as DateTime)

#1