SQL GROUP BY

SQL GROUP BY 语句与合计函数一起使用,根据数据表的一个或多个列对结果集进行分组。

SQL GROUP BY 语句语法

SELECT ColumnName1, ColumnName2, ... , aggregate_function (ColumnName)
FROM 表名
WHERE 条件
GROUP BY ColumnName1, ColumnName2, ... 

合计函数可以是COUNT, SUM, MAX, MIN, AVG, …

SQL GROUP BY 语句范例

数据表: DailyHours

EmployeeId EmployeeName Department WorkingDate Hours
203 Mazojys Fxoj Finance 2010-08-29 8
204 Jozzh Lnanyo Finance 2010-08-29 8
302 Jssme Bdnaa Development 2010-08-29 10
303 Dnnaao Errllov Development 2010-08-29 8
304 Tyoysww Osk Development 2010-08-29 9
203 Mazojys Fxoj Finance 2010-08-30 8
204 Jozzh Lnanyo Finance 2010-08-30 9
302 Jssme Bdnaa Development 2010-08-30 10
303 Dnnaao Errllov Development 2010-08-30 10
304 Tyoysww Osk Development 2010-08-30 9

例1
我们想找出每个部门的总小时数:

SELECT Department, SUM(Hours) AS TotalHours
FROM DailyHours
GROUP BY Department

结果会类似于:

Department TotalHours
Finance 33
Development 56

例2
有时我们需要对超过一个列使用GROUP BY语句,比如,我们想找出每个部门每天的总小时数:

SELECT Department, WorkingDate, SUM(Hours) AS TotalHours
FROM DailyHours
GROUP BY Department, WorkingDate

结果会类似于:

Department WorkingDate TotalHours
Finance 2010-08-29 16
Finance 2010-08-30 17
Development 2010-08-29 27
Development 2010-08-30 29