SQL GROUP BY 语句
SQL GROUP BY 语句
GROUP BY语句将具有相同值的行分组到汇总行中，例如“查找每个国家的客户数”。
GROUP BY语句通常与聚合函数（COUNT，MAX，MIN，SUM，AVG）一起使用，将结果集分组为一列或多列。
GROUP BY语法
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
演示数据库
以下是Northwind示例数据库中“Customers”表的选择：
| CustomerID |
CustomerName |
ContactName |
Address |
City |
PostalCode |
Country |
| 1 |
Alfreds Futterkiste |
Maria Anders |
Obere Str. 57 |
Berlin |
12209 |
Germany |
| 2 |
Ana Trujillo Emparedados y helados |
Ana Trujillo |
Avda. de la Constituciуn 2222 |
Mйxico D.F. |
05021 |
Mexico |
| 3 |
Antonio Moreno Taquerнa |
Antonio Moreno |
Mataderos 2312 |
Mйxico D.F. |
05023 |
Mexico |
| 4 |
Around the Horn |
Thomas Hardy |
120 Hanover Sq. |
London |
WA1 1DP |
UK |
| 5 |
Berglunds snabbkцp |
Christina Berglund |
Berguvsvдgen 8 |
Luleе |
S-958 22 |
Sweden |
SQL GROUP BY 实例
以下SQL语句列出了每个国家的客户数量：
实例
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;
亲自尝试»
以下SQL语句列出了每个国家的客户数量，从高到低排序：
实例
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;
亲自尝试»
演示数据库
以下是Northwind实例数据库中“Orders”表查询：
| OrderID |
CustomerID |
EmployeeID |
OrderDate |
ShipperID |
| 10248 |
90 |
5 |
1996-07-04 |
3 |
| 10249 |
81 |
6 |
1996-07-05 |
1 |
| 10250 |
34 |
4 |
1996-07-08 |
2 |
并从“Shippers”表中查询：
| ShipperID |
ShipperName |
| 1 |
Speedy Express |
| 2 |
United Package |
| 3 |
Federal Shipping |
GROUP BY 和 JOIN 实例
以下SQL语句列出了每个出货单发送的订单数：
实例
SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
GROUP BY ShipperName;
亲自尝试»
