SELECT - GROUP BY- Transact-SQLSELECT - GROUP BY- Transact-SQL

適用於: 是SQL Server 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

如果 SELECT 陳述式子句將查詢結果分成幾個資料列群組,通常是為了對每個群組執行一或多個彙總。A SELECT statement clause that divides the query result into groups of rows, usually for the purpose of performing one or more aggregations on each group. SELECT 陳述式會為每個群組傳回一個資料列。The SELECT statement returns one row per group.

語法Syntax

主題連結圖示 Transact-SQL 語法慣例 (Transact-SQL)Topic link icon Transact-SQL Syntax Conventions (Transact-SQL)

-- Syntax for SQL Server and Azure SQL Database   
-- ISO-Compliant Syntax  
  
GROUP BY {
      column-expression  
    | ROLLUP ( <group_by_expression> [ ,...n ] )  
    | CUBE ( <group_by_expression> [ ,...n ] )  
    | GROUPING SETS ( <grouping_set> [ ,...n ]  )  
    | () --calculates the grand total 
} [ ,...n ] 
 
<group_by_expression> ::=  
      column-expression  
    | ( column-expression [ ,...n ] )    
   
<grouping_set> ::=  
      () --calculates the grand total  
    | <grouping_set_item>  
    | ( <grouping_set_item> [ ,...n ] )  
  
<grouping_set_item> ::=  
      <group_by_expression>  
    | ROLLUP ( <group_by_expression> [ ,...n ] )  
    | CUBE ( <group_by_expression> [ ,...n ] )  
  

-- For backward compatibility only.
-- Non-ISO-Compliant Syntax for SQL Server and Azure SQL Database 
  
GROUP BY 
      [ ALL ] column-expression [ ,...n ] 
    | column-expression [ ,...n ] [ WITH { CUBE | ROLLUP } ]   

-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
  
GROUP BY {
      column-name [ WITH (DISTRIBUTED_AGG) ]  
    | column-expression
    | ROLLUP ( <group_by_expression> [ ,...n ] ) 
} [ ,...n ]

引數Arguments

column-expressioncolumn-expression

指定資料行,或在資料行進行非彙總計算。Specifies a column or a non-aggregate calculation on a column. 此資料行可以屬於資料表、衍生資料表或檢視。This column can belong to a table, derived table, or view. 資料行必須出現在 SELECT 陳述式的 FROM 子句中,但不需要出現在 SELECT 清單中。The column must appear in the FROM clause of the SELECT statement, but is not required to appear in the SELECT list.

如需有效的運算式,請參閱運算式For valid expressions, see expression.

資料行必須出現在 SELECT 陳述式的 FROM 子句中,但不需要出現在 SELECT 清單中。The column must appear in the FROM clause of the SELECT statement, but is not required to appear in the SELECT list. 不過 <select> 清單中任何非彙總運算式內的每一個資料表或檢視資料行都必須包含在 GROUP BY 清單內:However, each table or view column in any nonaggregate expression in the <select> list must be included in the GROUP BY list:

允許使用下列陳述式:The following statements are allowed:

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;  

不允許使用下列陳述式:The following statements are not allowed:

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

資料行運算式不能包含:The column expression cannot contain:

  • SELECT 清單中定義的資料行別名。A column alias that is defined in the SELECT list. 它可用於 FROM 子句中定義之衍生資料表的資料行別名。It can use a column alias for a derived table that is defined in the FROM clause.
  • textntextimage 類型的資料行。A column of type text, ntext, or image. 不過,您可以使用 text、ntext 或 image 的資料行做為函數的引數,傳回有效資料類型的值。However, you can use a column of text, ntext, or image as an argument to a function that returns a value of a valid data type. 例如,運算式可以使用 SUBSTRING() 與 CAST()。For example, the expression can use SUBSTRING() and CAST(). 這也適用於 HAVING 子句中的運算式。This also applies to expressions in the HAVING clause.
  • xml 資料類型方法。xml data type methods. 可以包含使用 xml 資料類型方法的使用者定義函數。It can include a user-defined function that uses xml data type methods. 可以包含使用 xml 資料類型方法的計算資料行。It can include a computed column that uses xml data type methods.
  • 子查詢。A subquery. 傳回錯誤 144。Error 144 is returned.
  • 索引檢視表的資料行。A column from an indexed view.

GROUP BY column-expression [ ,...n ]GROUP BY column-expression [ ,...n ]

根據一或多個資料行運算式的清單值,群組 SELECT 陳述式的結果。Groups the SELECT statement results according to the values in a list of one or more column expressions.

例如,此查詢會建立一個有 Country、Region 和 Sales 資料行的 Sales 資料表。For example, this query creates a Sales table with columns for Country, Region, and Sales. 查詢插入四個資料列,其中兩個資料列在 Country 和 Region 的值相同。It inserts four rows and two of the rows have matching values for Country and Region.

CREATE TABLE Sales ( Country varchar(50), Region varchar(50), Sales int );

INSERT INTO sales VALUES (N'Canada', N'Alberta', 100);
INSERT INTO sales VALUES (N'Canada', N'British Columbia', 200);
INSERT INTO sales VALUES (N'Canada', N'British Columbia', 300);
INSERT INTO sales VALUES (N'United States', N'Montana', 100);

Sales 資料表包含以下資料列:The Sales table contains these rows:

CountryCountry RegionRegion SalesSales
CanadaCanada AlbertaAlberta 100100
CanadaCanada British ColumbiaBritish Columbia 200200
CanadaCanada British ColumbiaBritish Columbia 300300
United StatesUnited States MontanaMontana 100100

下一個查詢群組 Country 和 Region,並傳回每個值組合的彙總總和。This next query groups Country and Region and returns the aggregate sum for each combination of values.

SELECT Country, Region, SUM(sales) AS TotalSales
FROM Sales
GROUP BY Country, Region;

查詢結果有 3 個資料列,因為 Country 和 Region 有 3 個值組合。The query result has 3 rows since there are 3 combinations of values for Country and Region. Canada 和 British Columbia 的 TotalSales 是兩個資料列的總和。The TotalSales for Canada and British Columbia is the sum of two rows.

CountryCountry RegionRegion TotalSalesTotalSales
CanadaCanada AlbertaAlberta 100100
CanadaCanada British ColumbiaBritish Columbia 500500
United StatesUnited States MontanaMontana 100100

GROUP BY ROLLUPGROUP BY ROLLUP

為每個資料行運算式的組合建立群組。Creates a group for each combination of column expressions. 此外,也會將結果「積存」到小計和總計。In addition, it "rolls up" the results into subtotals and grand totals. 為了執行,它會從右向左移動,減少建立群組和彙總的資料行運算式數目。To do this, it moves from right to left decreasing the number of column expressions over which it creates groups and the aggregation(s).

資料行順序會影響 ROLLUP 的輸出,也會影響結果集中的資料列數。The column order affects the ROLLUP output and can affect the number of rows in the result set.

例如,GROUP BY ROLLUP (col1, col2, col3, col4) 會為下列清單中的每個資料行運算式組合建立群組。For example, GROUP BY ROLLUP (col1, col2, col3, col4) creates groups for each combination of column expressions in the following lists.

  • col1, col2, col3, col4col1, col2, col3, col4
  • col1, col2, col3, NULLcol1, col2, col3, NULL
  • col1, col2, NULL, NULLcol1, col2, NULL, NULL
  • col1, NULL, NULL, NULLcol1, NULL, NULL, NULL
  • NULL, NULL, NULL, NULL -- 這是總計NULL, NULL, NULL, NULL --This is the grand total

此程式碼會使用先前範例的資料表,執行 GROUP BY ROLLUP 而不是簡單的 GROUP BY 作業。Using the table from the previous example, this code runs a GROUP BY ROLLUP operation instead of a simple GROUP BY.

SELECT Country, Region, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY ROLLUP (Country, Region);

查詢結果的彙總與簡單的 GROUP BY (沒有 ROLLUP) 的彙總相同。The query result has the same aggregations as the simple GROUP BY without the ROLLUP. 此外,會為每個 Country 的值建立小計。In addition, it creates subtotals for each value of Country. 最後,提供所有資料列的總計。Finally, it gives a grand total for all rows. 結果如下所示:The result looks like this:

CountryCountry RegionRegion TotalSalesTotalSales
CanadaCanada AlbertaAlberta 100100
CanadaCanada British ColumbiaBritish Columbia 500500
CanadaCanada NULLNULL 600600
United StatesUnited States MontanaMontana 100100
United StatesUnited States NULLNULL 100100
NULLNULL NULLNULL 700700

GROUP BY CUBE ( )GROUP BY CUBE ( )

GROUP BY CUBE 為所有可能的資料行組合建立群組。GROUP BY CUBE creates groups for all possible combinations of columns. 如果是 GROUP BY CUBE (a, b),結果會有 (a, b)、(NULL, b)、(a, NULL) 和 (NULL, NULL) 這些唯一值的群組。For GROUP BY CUBE (a, b) the results has groups for unique values of (a, b), (NULL, b), (a, NULL), and (NULL, NULL).

此程式碼使用先前範例的資料表,對 Country 和 Region 執行 GROUP BY CUBE 作業。Using the table from the previous examples, this code runs a GROUP BY CUBE operation on Country and Region.

SELECT Country, Region, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY CUBE (Country, Region);

查詢結果會有 (Country, Region)、(NULL, Region)、(Country, NULL) 和 (NULL, NULL) 這些唯一值的群組。The query result has groups for unique values of (Country, Region), (NULL, Region), (Country, NULL), and (NULL, NULL). 結果如下:The results look like this:

CountryCountry RegionRegion TotalSalesTotalSales
CanadaCanada AlbertaAlberta 100100
NULLNULL AlbertaAlberta 100100
CanadaCanada British ColumbiaBritish Columbia 500500
NULLNULL British ColumbiaBritish Columbia 500500
United StatesUnited States MontanaMontana 100100
NULLNULL MontanaMontana 100100
NULLNULL NULLNULL 700700
CanadaCanada NULLNULL 600600
United StatesUnited States NULLNULL 100100

GROUP BY GROUPING SETS ( )GROUP BY GROUPING SETS ( )

GROUPING SETS 選項可讓您將多個 GROUP BY 子句結合成一個 GROUP BY 子句。The GROUPING SETS option gives you the ability to combine multiple GROUP BY clauses into one GROUP BY clause. 結果等於指定之群組的 UNION ALL。The results are the equivalent of UNION ALL of the specified groups.

例如,GROUP BY ROLLUP (Country, Region)GROUP BY GROUPING SETS ( ROLLUP (Country, Region) ) 傳回相同的結果。For example, GROUP BY ROLLUP (Country, Region) and GROUP BY GROUPING SETS ( ROLLUP (Country, Region) ) return the same results.

當 GROUPING SETS 有兩個或多個元素時,結果是元素的聯集。When GROUPING SETS has two or more elements, the results are a union of the elements. 此範例傳回 Country 和 Region 之 ROLLUP 和 CUBE 結果的聯集。This example returns the union of the ROLLUP and CUBE results for Country and Region.

SELECT Country, Region, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY GROUPING SETS ( ROLLUP (Country, Region), CUBE (Country, Region) );

結果與此查詢 (傳回兩個 GROUP BY 陳述式的聯集) 相同。The results are the same as this query that returns a union of the two GROUP BY statements.

SELECT Country, Region, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY ROLLUP (Country, Region)
UNION ALL
SELECT Country, Region, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY CUBE (Country, Region);

SQL 不會合併針對 GROUPING SETS 清單所產生的重複群組。SQL does not consolidate duplicate groups generated for a GROUPING SETS list. 例如,在 GROUP BY ( (), CUBE (Country, Region) ) 中,兩個元素都傳回總計的資料列,但兩個資料列都會列在結果中。For example, in GROUP BY ( (), CUBE (Country, Region) ), both elements return a row for the grand total and both rows will be listed in the results.

GROUP BY ()GROUP BY ()

指定產生總計的空群組。Specifies the empty group which generates the grand total. 當其中一個元素是 GROUPING SET 時,這非常有用。This is useful as one of the elements of a GROUPING SET. 例如,此陳述式計算每個國家/地區的銷售總額,然後計算所有國家/地區的總計。For example, this statement gives the total sales for each country and then gives the grand-total for all countries.

SELECT Country, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY GROUPING SETS ( Country, () );

GROUP BY [ ALL ] column-expression [ ,...n ]GROUP BY [ ALL ] column-expression [ ,...n ]

適用於:SQL Server 和 Azure SQL DatabaseApplies to: SQL Server and Azure SQL Database

附註:提供此語法,只是為了回溯相容性。NOTE: This syntax is provided for backward compatibility only. 未來的版本將予以移除。It will be removed in a future version. 請避免在新的開發工作中使用這個語法,並規劃修改目前使用這個語法的應用程式。Avoid using this syntax in new development work, and plan to modify applications that currently use this syntax.

指定在結果中包含所有群組,不論它們是否符合 WHERE 子句中的搜尋準則。Specifies to include all groups in the results regardless of whether they meet the search criteria in the WHERE clause. 不符合搜尋準則的群組彙總會是 NULL。Groups that don't meet the search criteria have NULL for the aggregation.

GROUP BY ALL:GROUP BY ALL:

  • 在存取遠端資料表的查詢中,如果查詢中也有 WHERE 子句,就不支援。Is not supported in queries that access remote tables if there is also a WHERE clause in the query.
  • 在具有 FILESTREAM 屬性的資料行上將會失敗。Will fail on columns that have the FILESTREAM attribute.

WITH (DISTRIBUTED_AGG)WITH (DISTRIBUTED_AGG)

適用於:Azure SQL 資料倉儲與平行處理資料倉儲Applies to: Azure SQL Data Warehouse and Parallel Data Warehouse

DISTRIBUTED_AGG 查詢提示會強制使用大量平行處理 (MPP) 系統,在執行彙總之前轉散發特定資料行上的資料表。The DISTRIBUTED_AGG query hint forces the massively parallel processing (MPP) system to redistribute a table on a specific column before performing an aggregation. GROUP BY 子句中只有一個資料行可以有 DISTRIBUTED_AGG 查詢提示。Only one column in the GROUP BY clause can have a DISTRIBUTED_AGG query hint. 查詢完成後,轉散發的資料表就會卸除。After the query finishes, the redistributed table is dropped. 不會變更原始資料表。The original table is not changed.

附註:提供 DISTRIBUTED_AGG 查詢提示是為了舊版平行處理資料倉儲的回溯相容性,不會改善大部分查詢的效能。NOTE: The DISTRIBUTED_AGG query hint is provided for backwards compatibility with earlier Parallel Data Warehouse versions and will not improve performance for most queries. 根據預設,MPP 在需要時就已經轉散發資料,以增進彙總的效能。By default, MPP already redistributes data as necessary to improve performance for aggregations.

一般備註General Remarks

GROUP BY 如何與 SELECT 陳述式互動How GROUP BY interacts with the SELECT statement

SELECT 清單:SELECT list:

  • 向量彙總。Vector aggregates. 如果 SELECT 清單包括彙總函式,GROUP BY 會計算每個群組的摘要值。If aggregate functions are included in the SELECT list, GROUP BY calculates a summary value for each group. 這些稱為向量彙總。These are known as vector aggregates.
  • 相異彙總。Distinct aggregates. ROLLUP、CUBE 和 GROUPING SETS 支援彙總 AVG (DISTINCT column_name)、COUNT (DISTINCT column_name) 和 SUM (DISTINCT column_name)。The aggregates AVG (DISTINCT column_name), COUNT (DISTINCT column_name), and SUM (DISTINCT column_name) are supported with ROLLUP, CUBE, and GROUPING SETS.

WHERE 子句:WHERE clause:

  • SQL 會在執行任何群組作業之前,移除不符合 WHERE 子句之條件的資料列。SQL removes Rows that do not meet the conditions in the WHERE clause before any grouping operation is performed.

HAVING 子句:HAVING clause:

  • SQL 會使用 HAVING 子句以篩選結果集中的群組。SQL uses the having clause to filter groups in the result set.

ORDER BY 子句:ORDER BY clause:

  • 請使用 ORDER BY 子句來排序結果集。Use the ORDER BY clause to order the result set. GROUP BY 子句不會排序結果集,The GROUP BY clause does not order the result set.

NULL 值:NULL values:

  • 如果群組資料行包含 NULL 值,系統會把所有 NULL 值都視為相等,並將它們收集成單一群組。If a grouping column contains NULL values, all NULL values are considered equal and they are collected into a single group.

限制事項Limitations and Restrictions

適用於:SQL Server (自 2008 起) 和 Azure SQL 資料倉儲Applies to: SQL Server (starting with 2008) and Azure SQL Data Warehouse

最大容量Maximum capacity

對於使用 ROLLUP、CUBE 或 GROUPING SETS 的 GROUP BY 子句,運算式的最大數目為 32。For a GROUP BY clause that uses ROLLUP, CUBE, or GROUPING SETS, the maximum number of expressions is 32. 群組的最大數目為 4096 (212)。The maximum number of groups is 4096 (212). 下列範例因為 GROUP BY 子句超過 4096 個群組而失敗。The following examples fail because the GROUP BY clause has more than 4096 groups.

  • 下列範例產生 4097 (212 + 1) 個群組集合,因此將失敗。The following example generates 4097 (212 + 1) grouping sets and will fail.

    GROUP BY GROUPING SETS( CUBE(a1, ..., a12), b )  
    
  • 下列範例產生 4097 (212 + 1) 個群組,因此將失敗。The following example generates 4097 (212 + 1) groups and will fail. CUBE ()() 群組集合都會產生總計資料列,而且不會刪除重複的群組集合。Both CUBE () and the () grouping set produce a grand total row and duplicate grouping sets are not eliminated.

    GROUP BY GROUPING SETS( CUBE(a1, ..., a12), ())  
    
  • 此範例使用回溯相容語法。This example uses the backwards compatible syntax. 產生 8192 (213) 個群組集合,因此將失敗。It generates 8192 (213) grouping sets and will fail.

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

    如果是不包含 CUBE 或 ROLLUP 的回溯相容 GROUP BY 子句,分組項目數會受到 GROUP BY 資料行大小、彙總資料行,以及查詢所涉及的彙總值所限制。For backwards compatible GROUP BY clauses that do not contain CUBE or ROLLUP, the number of group by items is limited by the GROUP BY column sizes, the aggregated columns, and the aggregate values involved in the query. 這項限制起源於保存中繼查詢結果時所需要的中繼工作資料表之 8,060 位元組限制。This limit originates from the limit of 8,060 bytes on the intermediate worktable that is needed to hold intermediate query results. 當指定 CUBE 或 ROLLUP 時,最多只允許 12 個群組運算式。A maximum of 12 grouping expressions is permitted when CUBE or ROLLUP is specified.

ISO 和 ANSI SQL-2006 GROUP BY 功能的支援Support for ISO and ANSI SQL-2006 GROUP BY Features

GROUP BY 子句支援 SQL-2006 標準內包含的所有 GROUP BY 功能,但是以下語法例外:The GROUP BY clause supports all GROUP BY features that are included in the SQL-2006 standard with the following syntax exceptions:

  • GROUP BY 子句中不允許使用群組集合,除非它們屬於明確 GROUPING SETS 清單的一部分。Grouping sets are not allowed in the GROUP BY clause unless they are part of an explicit GROUPING SETS list. 例如,標準中允許 GROUP BY Column1, (Column2, ...ColumnN),但是 Transact-SQL 中不允許。For example, GROUP BY Column1, (Column2, ...ColumnN) is allowed in the standard but not in Transact-SQL. Transact-SQL 支援 GROUP BY C1, GROUPING SETS ((Column2, ...ColumnN))GROUP BY Column1, Column2, ... ColumnN,這兩者語意相等。Transact-SQL supports GROUP BY C1, GROUPING SETS ((Column2, ...ColumnN)) and GROUP BY Column1, Column2, ... ColumnN, which are semantically equivalent. 其語意相當於之前的 GROUP BY 範例。These are semantically equivalent to the previous GROUP BY example. 這是為了避免 GROUP BY Column1, (Column2, ...ColumnN) 可能錯誤地解譯為 GROUP BY C1, GROUPING SETS ((Column2, ...ColumnN)),這兩者語意不相等。This is to avoid the possibility that GROUP BY Column1, (Column2, ...ColumnN) might be misinterpreted as GROUP BY C1, GROUPING SETS ((Column2, ...ColumnN)), which are not semantically equivalent.

  • 群組集合內不允許使用群組集合。Grouping sets are not allowed inside grouping sets. 例如,SQL-2006 標準中允許 GROUP BY GROUPING SETS (A1, A2,...An, GROUPING SETS (C1, C2, ...Cn)),但是 Transact-SQL 中不允許。For example, GROUP BY GROUPING SETS (A1, A2,...An, GROUPING SETS (C1, C2, ...Cn)) is allowed in the SQL-2006 standard but not in Transact-SQL. Transact-SQL 允許 GROUP BY GROUPING SETS( A1, A2,...An, C1, C2, ...Cn )GROUP BY GROUPING SETS( (A1), (A2), ... (An), (C1), (C2), ... (Cn) ),這兩者在語意上與第一個 GROUP BY 範例相同,而且語法更清楚。Transact-SQL allows GROUP BY GROUPING SETS( A1, A2,...An, C1, C2, ...Cn ) or GROUP BY GROUPING SETS( (A1), (A2), ... (An), (C1), (C2), ... (Cn) ), which are semantically equivalent to the first GROUP BY example and have a more clear syntax.

  • GROUP BY [ALL/DISTINCT] 只能在包含資料行運算式的簡單 GROUP BY 子句中使用。GROUP BY [ALL/DISTINCT] is only allowed in a simple GROUP BY clause that contains column expressions. 不允許搭配 GROUPING SETS、ROLLUP、CUBE、WITH CUBE 或 WITH ROLLUP 建構。It is not allowed with the GROUPING SETS, ROLLUP, CUBE, WITH CUBE or WITH ROLLUP constructs. ALL 為預設值而且是隱含的。ALL is the default and is implicit. 也只能在回溯相容的語法中使用。It is also only allowed in the backwards compatible syntax.

比較支援的 GROUP BY 功能Comparison of Supported GROUP BY Features

下表描述根據 SQL 版本和資料庫相容性層級所支援的 GROUP BY 功能。The following table describes the GROUP BY features that are supported based upon SQL versions and database compatibility level.

功能Feature SQL Server Integration ServicesSQL Server Integration Services SQL Server 相容性層級 100 或更高層級SQL Server compatibility level 100 or higher 相容性層級 90 的 SQL Server 2008 或更新版本。SQL Server 2008 or later with compatibility level 90.
DISTINCT 彙總DISTINCT aggregates 不支援 WITH CUBE 或 WITH ROLLUP。Not supported for WITH CUBE or WITH ROLLUP. 支援 WITH CUBE、WITH ROLLUP、GROUPING SETS、CUBE 或 ROLLUP。Supported for WITH CUBE, WITH ROLLUP, GROUPING SETS, CUBE, or ROLLUP. 與相容性層級 100 相同。Same as compatibility level 100.
GROUP BY 子句中具有 CUBE 或 ROLLUP 名稱的使用者定義函數User-defined function with CUBE or ROLLUP name in the GROUP BY clause 允許在 GROUP BY 子句中使用使用者定義函式 dbo.cube( arg1 , ...argN ) or dbo.rollup( arg1 , ...argN )User-defined function dbo.cube(arg1,...argN) or dbo.rollup(arg1,...argN) in the GROUP BY clause is allowed.

例如: SELECT SUM (x) FROM T GROUP BY dbo.cube(y);For example: SELECT SUM (x) FROM T GROUP BY dbo.cube(y);
不允許在 GROUP BY 子句中使用使用者定義函式 dbo.cube ( arg1 , ...argN ) or dbo.rollup( arg1 , ...argN )User-defined function dbo.cube (arg1,...argN ) or dbo.rollup( arg1 ,...argN) in the GROUP BY clause is not allowed.

例如: SELECT SUM (x) FROM T GROUP BY dbo.cube(y);For example: SELECT SUM (x) FROM T GROUP BY dbo.cube(y);

系統會傳回下列錯誤訊息:「關鍵字 'cube'|'rollup' 附近的語法不正確。」The following error message is returned: "Incorrect syntax near the keyword 'cube'|'rollup'."

若要避免這個問題,請使用 dbo.cube 取代 [dbo].[cube],或使用 dbo.rollup 取代 [dbo].[rollup]To avoid this problem, replace dbo.cube with [dbo].[cube] or dbo.rollup with [dbo].[rollup].

允許使用下列範例:SELECT SUM (x) FROM T GROUP BY [dbo].[cube](y);The following example is allowed: SELECT SUM (x) FROM T GROUP BY [dbo].[cube](y);
允許在 GROUP BY 子句中使用使用者定義函式 dbo.cube ( arg1 , ...argN) or dbo.rollup( arg1 , ...argN )User-defined function dbo.cube (arg1,...argN) or dbo.rollup(arg1,...argN) in the GROUP BY clause is allowed

例如: SELECT SUM (x) FROM T GROUP BY dbo.cube(y);For example: SELECT SUM (x) FROM T GROUP BY dbo.cube(y);
GROUPING SETSGROUPING SETS 不支援Not supported 支援Supported 支援Supported
CUBECUBE 不支援Not supported 支援Supported 不支援Not supported
ROLLUPROLLUP 不支援Not supported 支援Supported 不支援Not supported
總計,例如 GROUP BY ()Grand total, such as GROUP BY () 不支援Not supported 支援Supported 支援Supported
GROUPING_ID 函數GROUPING_ID function 不支援Not supported 支援Supported 支援Supported
GROUPING 函數GROUPING function 支援Supported 支援Supported 支援Supported
WITH CUBEWITH CUBE 支援Supported 支援Supported 支援Supported
WITH ROLLUPWITH ROLLUP 支援Supported 支援Supported 支援Supported
移除 WITH CUBE 或 WITH ROLLUP 的重複群組WITH CUBE or WITH ROLLUP "duplicate" grouping removal 支援Supported 支援Supported 支援Supported

範例Examples

A.A. 使用簡單的 GROUP BY 子句Use a simple GROUP BY clause

下列範例會從 SalesOrderID 資料表中,擷取每個 SalesOrderDetail 的總計。The following example retrieves the total for each SalesOrderID from the SalesOrderDetail table. 這個範例會使用 AdventureWorks。This example uses AdventureWorks.

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

B.B. 使用 GROUP BY 子句搭配多個資料表Use a GROUP BY clause with multiple tables

下列範例會從聯結至 City 資料表的 Address 資料表中,擷取每個 EmployeeAddress 的員工人數。The following example retrieves the number of employees for each City from the Address table joined to the EmployeeAddress table. 這個範例會使用 AdventureWorks。This example uses AdventureWorks.

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.C. 使用 GROUP BY 子句搭配運算式Use a GROUP BY clause with an expression

下列範例會使用 DATEPART 函數,擷取每年的銷售總額。The following example retrieves the total sales for each year by using the DATEPART function. SELECT 清單和 GROUP BY 子句中必須出現相同的運算式。The same expression must be present in both the SELECT list and GROUP BY clause.

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.D. 使用 GROUP BY 子句搭配 HAVING 子句Use a GROUP BY clause with a HAVING clause

下列範例會使用 HAVING 子句,指定 GROUP BY 子句中產生的哪一個群組要包含在結果集中。The following example uses the HAVING clause to specify which of the groups generated in the GROUP BY clause should be included in the result set.

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);  

範例:SQL 資料倉儲與平行處理資料倉儲Examples: SQL Data Warehouse and Parallel Data Warehouse

E.E. GROUP BY 子句的基本使用Basic use of the GROUP BY clause

下列範例會尋找每天的所有銷售總額。The following example finds the total amount for all sales on each day. 每天只會傳回一個包含所有銷售總和的資料列。One row containing the sum of all sales is returned for each day.

-- Uses AdventureWorksDW  
  
SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales FROM FactInternetSales  
GROUP BY OrderDateKey ORDER BY OrderDateKey;  

F.F. DISTRIBUTED_AGG 提示的基本使用Basic use of the DISTRIBUTED_AGG hint

此範例使用 DISTRIBUTED_AGG 查詢提示在執行彙總之前,強制應用裝置輪換 CustomerKey 資料行上的資料表。This example uses the DISTRIBUTED_AGG query hint to force the appliance to shuffle the table on the CustomerKey column before performing the aggregation.

-- Uses AdventureWorksDW  
  
SELECT CustomerKey, SUM(SalesAmount) AS sas  
FROM FactInternetSales  
GROUP BY CustomerKey WITH (DISTRIBUTED_AGG)  
ORDER BY CustomerKey DESC;  

G.G. GROUP BY 的語法變化Syntax Variations for GROUP BY

當選取清單沒有彙總時,選取清單中的每個資料行都必須包含在 GROUP BY 清單內。When the select list has no aggregations, each column in the select list must be included in the GROUP BY list. GROUP BY 清單中可以列出選取清單中的計算資料行,但是並非必要。Computed columns in the select list can be listed, but are not required, in the GROUP BY list. 以下是句法有效的 SELECT 陳述式範例:These are examples of syntactically valid SELECT statements:

-- Uses AdventureWorks  
  
SELECT LastName, FirstName FROM DimCustomer GROUP BY LastName, FirstName;  
SELECT NumberCarsOwned FROM DimCustomer GROUP BY YearlyIncome, NumberCarsOwned;  
SELECT (SalesAmount + TaxAmt + Freight) AS TotalCost FROM FactInternetSales GROUP BY SalesAmount, TaxAmt, Freight;  
SELECT SalesAmount, SalesAmount*1.10 SalesTax FROM FactInternetSales GROUP BY SalesAmount;  
SELECT SalesAmount FROM FactInternetSales GROUP BY SalesAmount, SalesAmount*1.10;  

H.H. 使用 GROUP BY 搭配多個 GROUP BY 運算式Using a GROUP BY with multiple GROUP BY expressions

下列範例使用多個 GROUP BY 準則群組結果。The following example groups results using multiple GROUP BY criteria. 如果在每個 OrderDateKey 群組內,有一些子群組可以由 DueDateKey 區分,將會針對結果集定義新的群組。If, within each OrderDateKey group, there are subgroups that can be differentiated by DueDateKey, a new grouping will be defined for the result set.

-- Uses AdventureWorks  
  
SELECT OrderDateKey, DueDateKey, SUM(SalesAmount) AS TotalSales   
FROM FactInternetSales
GROUP BY OrderDateKey, DueDateKey   
ORDER BY OrderDateKey;  

I.I. 使用 GROUP BY 子句搭配 HAVING 子句Using a GROUP BY clause with a HAVING clause

下列範例使用 HAVING 子句,指定 GROUP BY 子句中產生,應包含在結果集中的群組。The following example uses the HAVING clause to specify the groups generated in the GROUP BY clause that should be included in the result set. 只有訂單日期在 2004 年或之後的群組才會包含在結果中。Only those groups with order dates in 2004 or later will be included in the results.

-- Uses AdventureWorks  
  
SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales   
FROM FactInternetSales  
GROUP BY OrderDateKey   
HAVING OrderDateKey > 20040000   
ORDER BY OrderDateKey;  

另請參閱See Also

GROUPING_ID (Transact-SQL) GROUPING_ID (Transact-SQL)
GROUPING (Transact-SQL) GROUPING (Transact-SQL)
SELECT (Transact-SQL) SELECT (Transact-SQL)
SELECT 子句 (Transact-SQL)SELECT Clause (Transact-SQL)