Sök…


Enkel gruppering

Beställningstabell

Kundnummer Serienummer Kvantitet Pris
1 2 5 100
1 3 2 200
1 4 1 500
2 1 4 50
3 5 6 700

Vid gruppering efter en specifik kolumn returneras endast unika värden för denna kolumn.

SELECT customerId
FROM orders
GROUP BY customerId;

Returvärde:

Kundnummer
1
2
3

Aggregerade funktioner som count() gäller för varje grupp och inte för hela tabellen:

SELECT customerId, 
       COUNT(productId) as numberOfProducts,
       sum(price) as totalPrice
FROM orders
GROUP BY customerId;

Returvärde:

Kundnummer numberOfProducts totalbelopp
1 3 800
2 1 50
3 1 700

GRUPPER AV flera kolumner

Man kanske vill gruppera mer än en kolumn

declare @temp table(age int, name varchar(15))

insert into @temp
select 18, 'matt' union all
select 21, 'matt' union all
select 21, 'matt' union all
select 18, 'luke' union all
select 18, 'luke' union all
select 21, 'luke' union all
select 18, 'luke' union all
select 21, 'luke'

SELECT Age, Name, count(1) count
FROM @temp 
GROUP BY Age, Name

kommer att grupperas efter både ålder och namn och kommer att producera:

Ålder namn räkna
18 luke 3
21 luke 2
18 matt 1
21 matt 2

Gruppera med flera tabeller, flera kolumner

Grupp av används ofta med anslutningsuttalande. Låt oss anta att vi har två bord. Den första är studenttabellen:

Id Fullständiga namn Ålder
1 Matt Jones 20
2 Frank Blue 21
3 Anthony Angel 18

Den andra tabellen är det ämnesbord som varje student kan ta:

Subject_Id Ämne
1 Matte
2 PE
3 Fysik

Och eftersom en student kan delta i många ämnen och ett ämne kan delta i många studenter (därför N: N-förhållande) måste vi ha en tredje "begränsande" tabell. Låt oss kalla tabellen Students_subjects:

Subject_Id Studentlegitimation
1 1
2 2
2 1
3 2
1 3
1 1

Låt oss nu säga att vi vill veta hur många ämnen varje student deltar i. Här är det fristående GROUP BY uttalandet inte tillräckligt eftersom informationen inte är tillgänglig via en enda tabell. Därför måste vi använda GROUP BY med JOIN uttalandet:

Select Students.FullName, COUNT(Subject Id) as SubjectNumber FROM Students_Subjects
LEFT JOIN Students
ON Students_Subjects.Student_id = Students.Id
GROUP BY Students.FullName

Resultatet av den givna frågan är som följer:

Fullständiga namn SubjectNumber
Matt Jones 3
Frank Blue 2
Anthony Angel 1

För ett ännu mer komplicerat exempel på användning av GROUP BY, låt oss säga att student kanske kan tilldela samma ämne till sitt namn mer än en gång (som visas i tabellen Students_Subjects). I det här scenariot kan vi kanske räkna antalet gånger varje ämne tilldelades en student genom att gruppera mer än en kolumn:

SELECT Students.FullName, Subjects.Subject,
COUNT(Students_subjects.Subject_id) AS NumberOfOrders
FROM ((Students_Subjects
INNER JOIN Students
ON Students_Subjcets.Student_id=Students.Id)
INNER JOIN Subjects
ON Students_Subjects.Subject_id=Subjects.Subject_id)
GROUP BY Fullname,Subject

Denna fråga ger följande resultat:

Fullständiga namn Ämne SubjectNumber
Matt Jones Matte 2
Matt Jones PE 1
Frank Blue PE 1
Frank Blue Fysik 1
Anthony Angel Matte 1

HAR

Eftersom WHERE klausulen utvärderas före GROUP BY , kan du inte använda WHERE att dela ner resultaten av grupperingarna (vanligtvis en sammanlagd funktion, till exempel COUNT(*) ). För att tillgodose detta behov kan HAVING klausulen användas.

Använd exempelvis följande data:

DECLARE @orders TABLE(OrderID INT, Name NVARCHAR(100))

INSERT INTO @orders VALUES
( 1, 'Matt' ),
( 2, 'John' ),
( 3, 'Matt' ),
( 4, 'Luke' ),
( 5, 'John' ),
( 6, 'Luke' ),
( 7, 'John' ),
( 8, 'John' ),
( 9, 'Luke' ),
( 10, 'John' ),
( 11, 'Luke' )

Om vi vill få antalet beställningar som varje person har gjort, skulle vi använda det

SELECT Name, COUNT(*) AS 'Orders'
FROM @orders
GROUP BY Name

och få

namn Order
Matt 2
John 5
Luke 4

Men om vi vill begränsa detta till individer som har gjort mer än två order kan vi lägga till en HAVING klausul.

SELECT Name, COUNT(*) AS 'Orders'
FROM @orders
GROUP BY Name
HAVING COUNT(*) > 2

Kommer att ge

namn Order
John 5
Luke 4

Observera att, precis som GROUP BY , kolumnerna i HAVING måste exakt matcha sina motsvarigheter i SELECT uttalandet. Om vi i stället ovan hade sagt

SELECT Name, COUNT(DISTINCT OrderID)

vår HAVING klausul skulle ha att säga

HAVING COUNT(DISTINCT OrderID) > 2

GRUPPER AV med ROLLUP och CUBE

ROLLUP-operatören är användbar för att generera rapporter som innehåller delsiffror och totaler.

  • CUBE genererar en resultatuppsättning som visar aggregat för alla kombinationer av värden i de valda kolumnerna.

  • ROLLUP genererar en resultatuppsättning som visar aggregat för en hierarki av värden i de valda kolumnerna.

    Artikel Färg Kvantitet
    Tabell Blå 124
    Tabell Röd 223
    Stol Blå 101
    Stol Röd 210
SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
            ELSE ISNULL(Item, 'UNKNOWN')
       END AS Item,
       CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
            ELSE ISNULL(Color, 'UNKNOWN')
       END AS Color,
       SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH ROLLUP

Item                 Color                QtySum                     
-------------------- -------------------- -------------------------- 
Chair                Blue                 101.00                     
Chair                Red                  210.00                     
Chair                ALL                  311.00                     
Table                Blue                 124.00                     
Table                Red                  223.00                     
Table                ALL                  347.00                     
ALL                  ALL                  658.00 

(7 påverkade rad (er))

Om ROLLUP-nyckelordet i frågan ändras till CUBE, är CUBE-resultatuppsättningen densamma, förutom att dessa två ytterligare rader returneras i slutet:

ALL                  Blue                 225.00                     
ALL                  Red                  433.00 

https://technet.microsoft.com/en-us/library/ms189305(v=sql.90).aspx



Modified text is an extract of the original Stack Overflow Documentation
Licensierat under CC BY-SA 3.0
Inte anslutet till Stack Overflow