SQL 基础教程

SQL 高级教程

SQL 函数

SQL 笔记

SQL GROUP BY 语句用法


SQL GROUP BY 语句将具有相同值的行分组,它通常和聚合函数(COUNTMAXMINSUMAVG)等一起使用。

GROUP BY 语句

GROUP BY 语句用于结合聚合函数,根据一个或多个列对结果集进行分组。

SQL GROUP BY 语法

SELECT column_name(s)
FROM table_name 
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

示例

简单应用

列出了每个国家/地区的客户数量:

SQL> SELECT country, COUNT(customer_id) FROM customers GROUP BY country;

执行后,输出如下:

+-----------+-------------------+
| country   | COUNT(customer_id)|  
+-----------+-------------------+
| Argentina | 3                 |
| Austria   | 2                 |
| Belgium   | 2                 |
| Brazil    | 9                 |
| Canada    | 3                 |
| Denmark   | 2                 |
+-----------+-------------------+

Join 联表分组

以下 SQL 语句列出了每个托运人发送的订单数:

SQL> SELECT s.shipper_name, COUNT(o.order_id) orders FROM orders o
LEFT JOIN shippers s ON o.shipper_id = s.shipper_id
GROUP BY s.shipper_name;

执行以上 SQL 输出结果如下:

+------------------+------------+
| shipper_name     | orders     |  
+------------------+------------+
| Federal Shipping | 68         |
| Speedy Express   | 54         |
| United Package   | 74         |
+------------------+------------+