共用方式為

GROUP BY (Transact-SQL)

  • 發行項

在 SQL Server 2014 中，根據一個或多個資料行或運算式的值，將一組選取的資料列分組成一組摘要資料列。 每一個群組都會傳回一個資料列。 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。

適用於：SQL Server (SQL Server 2008 透過目前版本)、Windows Azure SQL 資料庫 (初始版本，透過目前版本)。

主題連結圖示 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 彙總資料列，加上小計或超彙總資料列以及總計資料列。

    傳回的群組數目等於 <複合元素清單> 中的運算式數目再加 1。 例如，假設有以下的陳述式。

    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 會將運算式的所有排列群組輸出到 <複合元素清單>。

    產生的群組數目等於 (2n)，其中 n = <複合元素清單> 中的運算式數目。 例如，假設有以下的陳述式。

    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 傳回的群組。 <群組集合項目清單> 可包含 ROLLUP 或 CUBE。

  • ( )
    空的群組會產生總計。

不符合 ISO 標準的語法

  • ALL
    未來的 Microsoft SQL Server 版本將移除這項功能。請避免在新的開發工作中使用這項功能，並規劃修改目前使用這項功能的應用程式。 包括所有群組和結果集，即使沒有任何資料列符合 WHERE 子句所指定的搜尋條件也是如此。 當指定 ALL 時，不符合搜尋條件之群組的摘要資料行都會傳回 Null 值。 您不能搭配 CUBE 或 ROLLUP 運算子來指定 ALL。

    在存取遠端資料表的查詢中，如果查詢中也有 WHERE 子句，便不支援 GROUP BY ALL。 GROUP BY ALL 在具有 FILESTREAM 屬性的資料行上將會失敗。

  • 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 位元組限制。 當指定 CUBE 或 ROLLUP 時，最多只允許 12 個群組運算式。

    在 group_by_expression 中，無法直接指定 xml 資料類型方法。 相反地，請參閱其中使用 xml 資料類型方法的使用者自訂函數，或參閱使用這些方法的計算資料行。

  • WITH CUBE
    未來的 Microsoft SQL Server 版本將移除這項功能。請避免在新的開發工作中使用這項功能，並規劃修改目前使用這項功能的應用程式。 除了指定 GROUP BY 提供的一般資料列以外，還會指定結果集所導入的摘要資料列。 結果集中群組或子群組每個可能的組合，都會傳回一個 GROUP BY 摘要資料列。 請利用 GROUPING 函數來判斷結果集中的 Null 值是否為 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 Database Engine 會傳回錯誤訊息，並取消查詢。

備註

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 不能在索引檢視表中指定。

GROUP BY 或 HAVING 不能直接用於 ntext、text 或 image 的資料行上。 這些資料行可當做函數中的引數使用，該函數會傳回另一個資料類型的值，例如 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 2014 中，GROUP BY 子句無法在用於 GROUP BY 清單的運算式中包含子查詢。 系統會傳回錯誤 144。

SQL Server 2014 支援 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 2014 允許 GROUP BY GROUPING SETS( A1, A2,...An, C1, C2, ...Cn ) 或 GROUP BY GROUPING SETS( (A1), (A2), ... (An), (C1), (C2), ... (Cn) )。 這些範例在語意上與第一個 GROUP BY 範例相同，而且語法更清楚。

  • 一般 GROUP BY 子句中不允許使用 GROUP BY [ALL/DISTINCT] 或具有 GROUPING SETS、ROLLUP、CUBE、WITH CUBE 或 WITH ROLLUP 建構。 ALL 為預設值而且是隱含的。

比較支援的 GROUP BY 功能

下表描述根據 SQL Server 版本和資料庫相容性層級所支援的 GROUP BY 功能。

功能

SQL Server Integration Services

SQL Server 相容性層級 100 或更高層級

相容性層級 90 的 SQL Server 2008 或更新版本。

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 子句

下列範例會從 AdventureWorks2012 資料庫的 SalesOrderID 資料表中，擷取每個 SalesOrderDetail 的總計。

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

B.使用 GROUP BY 子句搭配多份資料表

下列範例會從聯結至 AdventureWorks2012 資料庫中 EmployeeAddress 資料表的 Address 資料表中，擷取每個 City 的員工人數。

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 子句中必須出現相同的運算式。

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 子句中產生的哪一個群組要包含在結果集中。

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)