VÄLJ – GRUPPERA EFTER Transact-SQL

Gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalysplattformssystem (PDW)SQL-analysslutpunkt i Microsoft FabricLager i Microsoft FabricSQL-databas i Förhandsversion av Microsoft Fabric

En SELECT-instruktionssats som delar upp frågeresultatet i grupper med rader, vanligtvis genom att utföra en eller flera aggregeringar i varje grupp. SELECT-instruktionen returnerar en rad per grupp.

Syntax

Transact-SQL syntaxkonventioner

-- 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 Synapse Analytics 
  
GROUP BY {
      column-name [ WITH (DISTRIBUTED_AGG) ]  
    | column-expression
    | ROLLUP ( <group_by_expression> [ ,...n ] ) 
} [ ,...n ]

-- Syntax for Parallel Data Warehouse  
  
GROUP BY {
      column-name [ WITH (DISTRIBUTED_AGG) ]  
    | column-expression
} [ ,...n ]

Arguments

column-expression

Anger en kolumn eller en icke-aggregerad beräkning för en kolumn. Den här kolumnen kan tillhöra en tabell, härledd tabell eller vy. Kolumnen måste visas i FROM-satsen i SELECT-instruktionen, men krävs inte för att visas i SELECT-listan.

Giltiga uttryck finns i uttrycket.

Kolumnen måste visas i FROM-satsen i SELECT-instruktionen, men krävs inte för att visas i SELECT-listan. Varje tabell eller vykolumn i ett icke-aggregeringsuttryck i urvalslistan <> måste dock inkluderas i LISTAN GROUP BY:

Följande instruktioner är tillåtna:

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;  

Följande instruktioner är inte tillåtna:

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

Kolumnuttrycket får inte innehålla:

• Ett kolumnalias som definieras i SELECT-listan. Den kan använda ett kolumnalias för en härledd tabell som definieras i FROM-satsen.
• En kolumn av typen text, ntext eller bild. Du kan dock använda en kolumn med text, ntext eller bild som ett argument till en funktion som returnerar ett värde av en giltig datatyp. Uttrycket kan till exempel använda SUBSTRING() och CAST(). Detta gäller även för uttryck i HAVING-satsen.
• xml-datatypsmetoder. Den kan innehålla en användardefinierad funktion som använder xml-datatypsmetoder. Den kan innehålla en beräknad kolumn som använder xml-datatypsmetoder.
• A subquery. Fel 144 returneras.
• En kolumn från en indexerad vy.

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

Grupperar SELECT-instruktionens resultat enligt värdena i en lista med ett eller flera kolumnuttryck.

Den här frågan skapar till exempel en sales-tabell med kolumner för Land, Region och Försäljning. Den infogar fyra rader och två av raderna har matchande värden för Land och 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);

Tabellen Försäljning innehåller följande rader:

Nästa fråga grupperar land och region och returnerar den aggregerade summan för varje kombination av värden.

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

Frågeresultatet har 3 rader eftersom det finns 3 kombinationer av värden för Land och Region. TotalSales för Kanada och British Columbia är summan av två rader.

GRUPPERA EFTER SAMMANSLAGNING

Skapar en grupp för varje kombination av kolumnuttryck. Dessutom "rullar" resultatet i delsummor och totalsummor. För att göra detta flyttas det från höger till vänster och minskar antalet kolumnuttryck som skapas i grupper och aggregeringarna.

Kolumnordningen påverkar ROLLUP-utdata och kan påverka antalet rader i resultatuppsättningen.

Skapar till exempel GROUP BY ROLLUP (col1, col2, col3, col4) grupper för varje kombination av kolumnuttryck i följande listor.

• col1, col2, col3, col4
• col1, col2, col3, NULL
• col1, col2, NULL, NULL
• col1, NULL, NULL, NULL
• NULL, NULL, NULL, NULL --Det här är totalsumman

Med hjälp av tabellen från föregående exempel kör den här koden en GROUP BY ROLLUP-åtgärd i stället för en enkel GROUP BY.

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

Frågeresultatet har samma sammansättningar som den enkla GROUP BY utan SAMMANSLAGNING. Dessutom skapar den delsummor för varje värde för Land. Slutligen ger den en totalsumma för alla rader. Resultatet ser ut så här:

GRUPPERA EFTER KUB ( )

GRUPPERA EFTER KUB skapar grupper för alla möjliga kombinationer av kolumner. För GROUP BY CUBE (a, b) har resultaten grupper för unika värden för (a, b), (NULL, b), (a, NULL) och (NULL, NULL).

Med hjälp av tabellen från föregående exempel kör den här koden en GROUP BY CUBE-åtgärd på land och region.

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

Frågeresultatet innehåller grupper för unika värden för (Land, Region), (NULL, Region), (Land, NULL) och (NULL, NULL). Resultatet ser ut så här:

GRUPPERA EFTER GRUPPERINGSUPPSÄTTNINGAR ( )

Alternativet GRUPPERINGSUPPSÄTTNINGAR ger dig möjlighet att kombinera flera GROUP BY-satser i en GROUP BY-sats. Resultatet motsvarar UNION ALL av de angivna grupperna.

Till exempel GROUP BY ROLLUP (Country, Region) och GROUP BY GROUPING SETS ( ROLLUP (Country, Region) ) returnera samma resultat.

När GRUPPERINGSUPPSÄTTNINGAR har två eller flera element är resultatet en union av elementen. I det här exemplet returneras en union av rollup- och CUBE-resultaten för land och region.

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

Resultatet är samma som den här frågan som returnerar en union av de två GROUP BY-uttrycken.

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 konsoliderar inte duplicerade grupper som genererats för en GRUPPUPPSÄTTNINGS-lista. I returnerar till exempel GROUP BY ( (), CUBE (Country, Region) )båda elementen en rad för totalsumman och båda raderna visas i resultatet.

GRUPPERA EFTER ()

Anger den tomma gruppen, som genererar totalsumman. Detta är användbart som ett av elementen i en GROUPING SET. Den här instruktionen ger till exempel den totala försäljningen för varje land/region och ger sedan totalsumman för alla länder/regioner.

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

GRUPPERA EFTER ALLA kolumnuttryck [ ,... n ]

Gäller för: SQL Server och Azure SQL Database

Anger att alla grupper ska inkluderas i resultaten oavsett om de uppfyller sökvillkoren i WHERE-satsen. Grupper som inte uppfyller sökvillkoren har NULL för aggregeringen.

GRUPPERA EFTER ALLA:

• Stöds inte i frågor som har åtkomst till fjärrtabeller om det också finns en WHERE-sats i frågan.
• Kommer att misslyckas för kolumner som har attributet FILESTREAM.

GROUP BY column-expression [ ,... n ] MED { KUB | SAMMANSLAGNING }

Gäller för: SQL Server och Azure SQL Database

WITH (DISTRIBUTED_AGG)

Gäller för: Azure Synapse Analytics and Analytics Platform System (PDW)

Det DISTRIBUTED_AGG frågetipset tvingar MPP-systemet (massively parallel processing) att omdistribuera en tabell i en specifik kolumn innan du utför en aggregering. Endast en kolumn i GROUP BY-satsen kan ha ett DISTRIBUTED_AGG frågetips. När frågan är klar tas den omdelade tabellen bort. Den ursprungliga tabellen ändras inte.

Obs! Det DISTRIBUTED_AGG frågetipset tillhandahålls för bakåtkompatibilitet med tidigare PDW-versioner (Analytics Platform System) och förbättrar inte prestandan för de flesta frågor. Som standard distribuerar MPP redan data efter behov för att förbättra prestanda för aggregeringar.

General Remarks

Så interagerar GROUP BY med SELECT-instruktionen

SELECT list:

• Vector aggregates. Om mängdfunktioner ingår i SELECT-listan beräknar GROUP BY ett sammanfattningsvärde för varje grupp. Dessa kallas vektoraggregat.
• Distinct aggregates. Aggregeringarna AVG (DISTINCT column_name), COUNT (DISTINCT column_name) och SUM (DISTINCT column_name) stöds med SAMMANSLAGNINGS-, KUB- och GRUPPERINGSUPPSÄTTNINGAR.

WHERE clause:

• SQL tar bort rader som inte uppfyller villkoren i WHERE-satsen innan någon grupperingsåtgärd utförs.

HAVING clause:

• SQL använder ha-satsen för att filtrera grupper i resultatuppsättningen.

ORDER BY-sats:

• Använd ORDER BY-satsen för att beställa resultatuppsättningen. GROUP BY-satsen beställer inte resultatuppsättningen.

NULL values:

• Om en grupperingskolumn innehåller NULL-värden anses alla NULL-värden vara lika och de samlas in i en enda grupp.

Begränsningar och restriktioner

Gäller för: SQL Server (från och med 2008) och Azure Synapse Analytics

Maximum capacity

För en GROUP BY-sats som använder ROLLUP, CUBE eller GROUPING SETS är det maximala antalet uttryck 32. Det maximala antalet grupper är 4 096 (2¹²). Följande exempel misslyckas eftersom GROUP BY-satsen har fler än 4 096 grupper.

• I följande exempel genereras grupperingsuppsättningarna 4097 (2¹² + 1) och misslyckas.

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

• I följande exempel genereras 4097-grupper (2¹² + 1) och misslyckas. Både CUBE () och grupperingsuppsättningen () skapar en total summarad och dubblettgrupperingsuppsättningar elimineras inte.

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

• I det här exemplet används den bakåtkompatibla syntaxen. Den genererar grupperingsuppsättningar för 8192 (2¹³) och misslyckas.

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

  För bakåtkompatibla GROUP BY-satser som inte innehåller KUB eller SAMMANSLAGNING begränsas antalet grupper efter objekt av kolumnstorlekarna GROUP BY, de aggregerade kolumnerna och de mängdvärden som ingår i frågan. Den här gränsen kommer från gränsen på 8 060 byte på den mellanliggande arbetstabell som behövs för att lagra mellanliggande frågeresultat. Högst 12 grupperingsuttryck tillåts när KUB eller SAMMANSLAGNING anges.

Stöd för ISO- och ANSI SQL-2006 GROUP BY-funktioner

GROUP BY-satsen stöder alla GROUP BY-funktioner som ingår i SQL-2006-standarden med följande syntaxfel:

• Grupperingsuppsättningar tillåts inte i GROUP BY-satsen såvida de inte ingår i en explicit GRUPPUPPSÄTTNINGS-lista. Till exempel GROUP BY Column1, (Column2, ...ColumnN) tillåts i standarden men inte i Transact-SQL. Transact-SQL stöder GROUP BY C1, GROUPING SETS ((Column2, ...ColumnN)) och GROUP BY Column1, Column2, ... ColumnN, som är semantiskt likvärdiga. Dessa är semantiskt likvärdiga med föregående GROUP BY exempel. Detta är för att undvika möjligheten att GROUP BY Column1, (Column2, ...ColumnN) kan misstolkas som GROUP BY C1, GROUPING SETS ((Column2, ...ColumnN)), som inte är semantiskt likvärdiga.

• Grupperingsuppsättningar tillåts inte i grupperingsuppsättningar. Till exempel tillåts i SQL-2006-standarden men GROUP BY GROUPING SETS (A1, A2,...An, GROUPING SETS (C1, C2, ...Cn)) inte i Transact-SQL. Transact-SQL tillåter GROUP BY GROUPING SETS( A1, A2,...An, C1, C2, ...Cn ) eller GROUP BY GROUPING SETS( (A1), (A2), ... (An), (C1), (C2), ... (Cn) ), som är semantiskt likvärdiga med det första GROUP BY-exemplet och har en tydligare syntax.

• GROUP BY [ALL/DISTINCT] tillåts endast i en enkel GROUP BY-sats som innehåller kolumnuttryck. Det är inte tillåtet med konstruktionerna GROUPING SETS, ROLLUP, CUBE, WITH CUBE eller WITH ROLLUP. ALL är standard och är implicit. Det tillåts också endast i den bakåtkompatibla syntaxen.

Jämförelse av GROUP BY-funktioner som stöds

I följande tabell beskrivs de GROUP BY-funktioner som stöds baserat på SQL-versioner och databaskompatibilitetsnivå.

Examples

A. Använda en enkel GROUP BY-sats

I följande exempel hämtas summan för var och en SalesOrderID från SalesOrderDetail tabellen. I det här exemplet används AdventureWorks.

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

B. Använda en GROUP BY-sats med flera tabeller

I följande exempel hämtas antalet anställda för var och en City från tabellen Address som är ansluten till EmployeeAddress tabellen. I det här exemplet används 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. Använda en GROUP BY-sats med ett uttryck

I följande exempel hämtas den totala försäljningen för varje år med hjälp DATEPART av funktionen . Samma uttryck måste finnas i både SELECT listan och GROUP BY -satsen.

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. Använda en GROUP BY-sats med en HAVING-sats

I följande exempel används HAVING -satsen för att ange vilka av grupperna som genereras i GROUP BY -satsen som ska ingå i resultatuppsättningen.

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

Exempel: Azure Synapse Analytics och Parallel Data Warehouse

E. Grundläggande användning av GROUP BY-satsen

I följande exempel hittar du det totala beloppet för all försäljning varje dag. En rad som innehåller summan av all försäljning returneras för varje dag.

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

F. Grundläggande användning av DISTRIBUTED_AGG tips

I det här exemplet används DISTRIBUTED_AGG frågetips för att tvinga installationen att blanda tabellen i CustomerKey kolumnen innan du utför aggregeringen.

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

G. Syntaxvariationer för GROUP BY

När den valda listan inte har några aggregeringar måste varje kolumn i urvalslistan inkluderas i LISTAN GROUP BY. Beräknade kolumner i urvalslistan kan visas, men krävs inte, i LISTAN GROUP BY. Det här är exempel på syntaktiskt giltiga SELECT-instruktioner:

-- 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. Använda en GROUP BY med flera GROUP BY-uttryck

I följande exempel grupperas resultat med hjälp av flera GROUP BY villkor. Om det inom varje OrderDateKey grupp finns undergrupper som kan särskiljas av DueDateKey, definieras en ny gruppering för resultatuppsättningen.

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

I. Använda en GROUP BY-sats med en HAVING-sats

I följande exempel används HAVING -satsen för att ange de grupper som genereras i GROUP BY satsen som ska ingå i resultatuppsättningen. Endast de grupper med orderdatum 2004 eller senare inkluderas i resultaten.

-- 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 (Transact-SQL)
SELECT (Transact-SQL)
SELECT-klausul (Transact-SQL)