通过

GROUP BY (Transact-SQL)

  • 项目

按 SQL Server 2012 中一个或多个列或表达式的值将一组选定行组合成一个摘要行集。 针对每一组返回一行。 SELECT 子句 <select> 列表中的聚合函数提供有关每个组（而不是各行）的信息。

GROUP BY 子句具有符合 ISO 的语法和不符合 ISO 的语法。 在一条 SELECT 语句中只能使用一种语法样式。 对于所有的新工作，请使用符合 ISO 的语法。 提供不符合 ISO 的语法的目的是为了实现向后兼容。

在本主题中，GROUP BY 子句可以描述为常规或简单子句：

  • 常规 GROUP BY 子句包括 GROUPING SETS、CUBE、ROLLUP、WITH CUBE 或 WITH ROLLUP。

  • 简单 GROUP BY 子句不包括 GROUPING SETS、CUBE、ROLLUP、WITH CUBE 或 WITH ROLLUP。 GROUP BY ()（也就是总计）被视为简单 GROUP BY。

主题链接图标 Transact-SQL 语法约定 (Transact-SQL)

语法

ISO-Compliant Syntax

GROUP BY <group by spec>

<group by spec> ::=
    <group by item> [ ,...n ]

<group by item> ::=
    <simple group by item>
    | <rollup spec>
    | <cube spec>
    | <grouping sets spec>
    | <grand total>

<simple group by item> ::=
    <column_expression>

<rollup spec> ::=
    ROLLUP ( <composite element list> ) 

<cube spec> ::=
    CUBE ( <composite element list> ) 

<composite element list> ::=
    <composite element> [ ,...n ]

<composite element> ::=
    <simple group by item>
    | ( <simple group by item list> ) 

<simple group by item list> ::=
    <simple group by item> [ ,...n ]

<grouping sets spec> ::=
    GROUPING SETS ( <grouping set list> ) 

<grouping set list> ::=
    <grouping set> [ ,...n ]

<grouping set> ::=
    <grand total>
    | <grouping set item>
    | ( <grouping set item list> ) 

<empty group> ::= 
        ( ) 

<grouping set item> ::=
    <simple group by item>
    | <rollup spec>
    | <cube spec>

<grouping set item list> ::=
    <grouping set item> [ ,...n ]

Non-ISO-Compliant Syntax
[ GROUP BY [ ALL ] group_by_expression [ ,...n ]
    [ WITH { CUBE | ROLLUP } ] 
]

参数

  • <column_expression>
    针对其执行分组操作的表达式。

  • ROLLUP ( )
    生成简单的 GROUP BY 聚合行以及小计行或超聚合行，还生成一个总计行。

    返回的分组数等于 <composite element list> 中的表达式数加一。 例如，请考虑下面的语句。

    SELECT a, b, c, SUM ( <expression> )
FROM T
GROUP BY ROLLUP (a,b,c);

    会为 (a, b, c)、(a, b) 和 (a) 值的每个唯一组合生成一个带有小计的行。 还将计算一个总计行。

    列是按照从右到左的顺序汇总的。 列的顺序会影响 ROLLUP 的输出分组，而且可能会影响结果集内的行数。

  • CUBE ( )
    生成简单的 GROUP BY 聚合行、ROLLUP 超聚合行和交叉表格行。

    CUBE 针对 <composite element list> 中表达式的所有排列输出一个分组。

    生成的分组数等于 (2n)，其中 n = <composite element list> 中的表达式数。 例如，请考虑下面的语句。

    SELECT a, b, c, SUM (<expression>)
FROM T
GROUP BY CUBE (a,b,c);

    会为 (a, b, c)、(a, b)、(a, c)、(b, c)、(a)、(b) 和 (c) 值的每个唯一组合生成一个带有小计的行，还会生成一个总计行。

    列的顺序不影响 CUBE 的输出。

  • GROUPING SETS ( )
    在一个查询中指定数据的多个分组。 仅聚合指定组，而不聚合由 CUBE 或 ROLLUP 生成的整组聚合。 其结果与针对指定的组执行 UNION ALL 运算等效。 GROUPING SETS 可以包含单个元素或元素列表。 GROUPING SETS 可以指定与 ROLLUP 或 CUBE 返回的内容等效的分组。 <grouping set item list> 可以包含 ROLLUP 或 CUBE。

  • ( )
    空组生成总计。

不符合 ISO 的语法

  • ALL
    后续版本的 Microsoft SQL Server 将删除该功能。请避免在新的开发工作中使用该功能，并着手修改当前还在使用该功能的应用程序。包含所有组和结果集，甚至包含那些其中任何行都不满足 WHERE 子句指定的搜索条件的组和结果集。 如果指定了 ALL，将对组中不满足搜索条件的汇总列返回空值。 不能用 CUBE 或 ROLLUP 运算符指定 ALL。

    如果在访问远程表的查询中还有 WHERE 子句，则该查询不支持 GROUP BY ALL。 对于具有 FILESTREAM 属性的列，GROUP BY ALL 将失败。

  • group_by_expression
    针对其执行分组操作的表达式。 group_by_expression 也称为分组列。 group_by expression 可以是列，也可以是引用由 FROM 子句返回的列的非聚合表达式。 不能使用在 SELECT 列表中定义的列别名来指定组合列。

    注意注意

    不能在 group_by_expression 中使用类型为 text、ntext 和 image 的列。

    对于不包含 CUBE 或 ROLLUP 的 GROUP BY 子句，group_by_expression 的项数受查询所涉及的 GROUP BY 列的大小、聚合列和聚合值的限制。 该限制从 8,060 字节的限制开始，对保存中间查询结果所需的中间级工作表有 8,060 字节的限制。 如果指定了 CUBE 或 ROLLUP，则最多只能有 12 个分组表达式。

    不能直接在 group_by_expression 中指定 xml 数据类型方法。 相反，可引用内部使用 xml 数据类型方法的用户定义函数，或引用使用这些数据类型方法的计算列。

  • WITH CUBE
    后续版本的 Microsoft SQL Server 将删除该功能。请避免在新的开发工作中使用该功能，并着手修改当前还在使用该功能的应用程序。指定结果集内不仅包含由 GROUP BY 提供的行，同时还包含汇总行。 GROUP BY 汇总行针对每个可能的组和子组组合在结果集内返回。 使用 GROUPING 函数可确定结果集内的空值是否为 GROUP BY 汇总值。

    结果集内的汇总行数取决于 GROUP BY 子句内包含的列数。 由于 CUBE 返回每个可能的组和子组组合，因此不论在列分组时指定使用什么顺序，行数都相同。

  • WITH ROLLUP
    后续版本的 Microsoft SQL Server 将删除该功能。请避免在新的开发工作中使用该功能，并着手修改当前还在使用该功能的应用程序。指定结果集内不仅包含由 GROUP BY 提供的行，同时还包含汇总行。 按层次结构顺序，从组内的最低级别到最高级别汇总组。 组的层次结构取决于列分组时指定使用的顺序。 更改列分组的顺序会影响在结果集内生成的行数。

    重要说明重要提示

    使用 CUBE 或 ROLLUP 时，不支持非重复聚合，如 AVG(DISTINCT column_name)、COUNT(DISTINCT column_name) 和 SUM(DISTINCT column_name)。 如果使用此类聚合，则 SQL Server 数据库引擎将返回错误消息并取消查询。

注释

GROUP BY 子句中的表达式可以包含 FROM 子句中表、派生表或视图的列。 这些列不必显示在 SELECT 子句 <select> 列表中。

<select> 列表中任何非聚合表达式中的每个表列或视图列都必须包括在 GROUP BY 列表中：

  • 允许使用下面的语句：

    SELECT ColumnA, ColumnB FROM T GROUP BY ColumnA, ColumnB;
SELECT ColumnA + ColumnB FROM T GROUP BY ColumnA, ColumnB;
SELECT ColumnA + ColumnB FROM T GROUP BY ColumnA + ColumnB;
SELECT ColumnA + ColumnB + constant FROM T GROUP BY ColumnA, ColumnB;

  • 不允许使用下面的语句：

    SELECT ColumnA, ColumnB FROM T GROUP BY ColumnA + ColumnB;
SELECT ColumnA + constant + ColumnB FROM T GROUP BY ColumnA + ColumnB;

如果 SELECT 子句 <select list> 中包含聚合函数，则 GROUP BY 将计算每组的汇总值。 这些函数称为矢量聚合。

执行任何分组操作之前，不满足 WHERE 子句中条件的行将被删除。

HAVING 子句与 GROUP BY 子句一起用来筛选结果集内的组。

GROUP BY 子句不能对结果集进行排序。 使用 ORDER BY 子句可以对结果集进行排序。

如果组合列包含 Null 值，则所有的 Null 值都将被视为相等，并会置入一个组中。

不能使用带有别名的 GROUP BY 来替换 AS 子句中的列名，除非别名将替换 FROM 子句内派生表中的列名。

将不删除 GROUPING SETS 列表中的重复分组集。 在以下情况下可能会生成重复分组集：多次指定一个列表达式，或者在 GROUPING SETS 列表中列出同样由 CUBE 或 ROLLUP 生成的列表达式。

ROLLUP、CUBE 和 GROUPING SETS 支持区分聚合，例如，AVG (DISTINCT column_name)、COUNT (DISTINCT column_name) 和 SUM (DISTINCT column_name)。

不能在索引视图中指定 ROLLUP、CUBE 和 GROUPING SETS。

不能直接针对具有 ntext、text 或 image 的列使用 GROUP BY 或 HAVING。 这些列可以在返回其他数据类型的函数（如 SUBSTRING() 和 CAST()）中用作参数。

不能直接在 <column_expression> 中指定 xml 数据类型方法。 相反，可引用内部使用 xml 数据类型方法的用户定义函数，或引用使用这些数据类型方法的计算列。

对于 GROUPING SETS、ROLLUP 和 CUBE 的 GROUP BY 限制

语法限制

在 GROUP BY 子句中，不允许使用 GROUPING SETS，除非它们是 GROUPING SETS 列表的一部分。 例如，不允许使用 GROUP BY C1, (C2,..., Cn)，但允许使用 GROUP BY GROUPING SETS (C1, (C2, ..., Cn))。

不允许在 GROUPING SETS 内部使用 GROUPING SETS。 例如，不允许使用 GROUP BY GROUPING SETS (C1, GROUPING SETS (C2, C3))。

在具有 ROLLUP、CUBE 或 GROUPING SETS 关键字的 GROUP BY 子句中，不允许使用不符合 ISO 的 ALL、WITH CUBE 和 WITH ROLLUP 关键字。

大小限制

对于简单的 GROUP BY 子句，针对表达式数量没有任何限制。

对于使用 ROLLUP、CUBE 或 GROUPING SETS 的 GROUP BY 子句，表达式的最大数量是 32，可以生成的分组集的最大数量是 4096 (212)。 在下面的示例中，由于 GROUP BY 子句太复杂，因此这些示例失败：

  • 下面的示例生成 8192 (213) 个分组集。

    GROUP BY CUBE (a1, ..., a13) 
GROUP BY a1, ..., a13 WITH CUBE

  • 下面的示例生成 4097 (212 + 1) 个分组集。

    GROUP BY GROUPING SETS( CUBE(a1, ..., a12), b )

  • 下面的示例也生成 4097 (212 + 1) 个分组集。 CUBE () 和 () 分组集会生成一个总计行，而且将不删除重复的分组集。

    GROUP BY GROUPING SETS( CUBE(a1, ..., a12), ())

对 ISO 和 ANSI SQL-2006 GROUP BY 功能的支持

在 SQL Server 2012 中，GROUP BY 子句无法在表达式中包含用于按列表进行分组的子查询。 返回错误 144。

除了下面的语法，SQL Server 2012 支持 SQL-2006 标准中包括的所有 GROUP BY 功能：

  • 不允许在 GROUP BY 子句中使用分组集，除非它们是显式 GROUPING SETS 列表的一部分。 例如，在标准中允许使用 GROUP BY Column1, (Column2, ...ColumnN)，但在 SQL Server 中不允许使用。 允许使用 GROUP BY C1, GROUPING SETS ((Column2, ...ColumnN)) 或 GROUP BY Column1, Column2, ... ColumnN。 这些示例与上面的 GROUP BY 示例在语义上等效。 这是为了避免 GROUP BY Column1, (Column2, ...ColumnN) 可能被误解为 GROUP BY C1, GROUPING SETS ((Column2, ...ColumnN))。 这在语义上是不等效的。

  • 不允许在分组集内部使用分组集。 例如，在 SQL-2006 标准中允许使用 GROUP BY GROUPING SETS (A1, A2,…An, GROUPING SETS (C1, C2, ...Cn))，但在 SQL Server 中不允许使用。 SQL Server 2012 允许使用 GROUP BY GROUPING SETS( A1, A2,...An, C1, C2, ...Cn ) 或 GROUP BY GROUPING SETS( (A1), (A2), ... (An), (C1), (C2), ... (Cn) )。 这些示例与第一个 GROUP BY 示例在语义上等效，但其语法更清楚。

  • GROUP BY [ALL/DISTINCT] 不能用于常规 GROUP BY 子句，也不能与 GROUPING SETS、ROLLUP、CUBE、WITH CUBE 或 WITH ROLLUP 构造一起使用。 ALL 是隐式默认值。

对支持的 GROUP BY 功能的比较

下表描述了不同的 SQL Server 版本以及数据库兼容级别支持的 GROUP BY 功能。

功能

SQL Server 2005 Integration Services

SQL Server 兼容级别 100 或更高

SQL Server 2008 或兼容级别为 90 的更高版本

DISTINCT 聚合

WITH CUBE 或 WITH ROLLUP 不支持。

WITH CUBE、WITH ROLLUP、GROUPING SETS、CUBE 或 ROLLUP 支持。

与兼容级别 100 相同。

GROUP BY 子句中具有 CUBE 或 ROLLUP 名称的用户定义函数

在 GROUP BY 子句中允许使用用户定义函数 dbo.cube (arg1,...argN) 或 dbo.rollup (arg1,...argN)。

例如：

SELECT SUM (x)
FROM T 
GROUP BY dbo.cube(y);

在 GROUP BY 子句中不允许使用用户定义函数 dbo.cube (arg1,...argN) 或 dbo.rollup (arg1,...argN)。

例如：

SELECT SUM (x)
FROM T 
GROUP BY dbo.cube(y);

返回下面的错误消息：“关键字 'cube'|'rollup' 附近有语法错误”。

为了避免出现此问题，请将 dbo.cube 替换为 [dbo].[cube] 或将 dbo.rollup 替换为 [dbo].[rollup]。

允许使用下面的示例：

SELECT SUM (x)
FROM T 
GROUP BY [dbo].[cube](y);

在 GROUP BY 子句中允许使用用户定义函数 dbo.cube (arg1,...argN) 或 dbo.rollup (arg1,...argN)。

例如：

SELECT SUM (x)
FROM T 
GROUP BY dbo.cube(y);

GROUPING SETS

不支持

支持

支持

CUBE

不支持

支持

不支持

ROLLUP

不支持

支持

不支持

总计，如 GROUP BY ()

不支持

支持

支持

GROUPING_ID 函数

不支持

支持

支持

GROUPING 函数

支持

支持

支持

WITH CUBE

支持

支持

支持

WITH ROLLUP

支持

支持

支持

WITH CUBE 或 WITH ROLLUP“重复”分组删除

支持

支持

支持

示例

A.使用简单 GROUP BY 子句

以下示例检索 SalesOrderDetail 表中各 SalesOrderID 的总数。

USE AdventureWorks2012;
GO
SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail AS sod
GROUP BY SalesOrderID
ORDER BY SalesOrderID;

B.将 GROUP BY 子句用于多个表

下面的示例检索与 EmployeeAddress 表联接的 Address 表中的各 City 的雇员数。

USE AdventureWorks2012;
GO
SELECT a.City, COUNT(bea.AddressID) EmployeeCount
FROM Person.BusinessEntityAddress AS bea 
    INNER JOIN Person.Address AS a
        ON bea.AddressID = a.AddressID
GROUP BY a.City
ORDER BY a.City;

C.将 GROUP BY 子句用于表达式

以下示例使用 DATEPART 函数检索每年的销售总额。 SELECT 列表和 GROUP BY 子句中必须有相同的表达式。

USE AdventureWorks2012;
GO
SELECT DATEPART(yyyy,OrderDate) AS N'Year'
    ,SUM(TotalDue) AS N'Total Order Amount'
FROM Sales.SalesOrderHeader
GROUP BY DATEPART(yyyy,OrderDate)
ORDER BY DATEPART(yyyy,OrderDate);

D.将 GROUP BY 子句与 HAVING 子句一起使用

下面的示例使用 HAVING 子句来指定应当将 GROUP BY 子句中生成的哪个组包括在结果集内。

USE AdventureWorks2012;
GO
SELECT DATEPART(yyyy,OrderDate) AS N'Year'
    ,SUM(TotalDue) AS N'Total Order Amount'
FROM Sales.SalesOrderHeader
GROUP BY DATEPART(yyyy,OrderDate)
HAVING DATEPART(yyyy,OrderDate) >= N'2003'
ORDER BY DATEPART(yyyy,OrderDate);

请参阅

参考

GROUPING_ID (Transact-SQL)

GROUPING (Transact-SQL)

SELECT (Transact-SQL)

SELECT 子句 (Transact-SQL)