OceanBase Cloud快速入门第60期：如何分组查询?

欢迎访问 OceanBase 官网获取更多信息：https://www.oceanbase.com/

GROUP BY 子句用于对查询结果进行分组。

分组查询语法

GROUP BY 支持单字段分组和多字段分组，使用 WHERE 子句可以在分组前对数据进行筛选，使用 HAVING 子句可以在分组后对数据进行筛选，使用 ORDER BY 子句可以在分组后对数据进行排序。分组查询的 SQL 语句格式如下：

SELECT select_list FROM table_list 
  [WHERE query_condition]
  GROUP BY group_by_expression
  [HAVING group_condition];

select_list：
  column_name, group_function,...

语法的参数说明如下：

• group_function 表示聚合函数。

• group_by_expression 表示分组表达式，多个表达式之间用逗号隔开。

• group_condition 表示分组之后对数据进行过滤。

在分组查询中，SELECT 后面的列只能是出现在GROUP BY 后面的列或者使用聚合函数的列。

分组中常用的聚合函数如下表所示。

分组查询示例

创建示例表 fruit_order 并插入适当数据。

CREATE TABLE fruit_order(
  order_id INT NOT NULL AUTO_INCREMENT COMMENT '订单ID',
  user_id BIGINT NOT NULL COMMENT '客户ID',
  user_name VARCHAR(16) NOT NULL DEFAULT '' COMMENT '客户名称',
  fruit_price DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '订单金额',
  order_year SMALLINT NOT NULL COMMENT '下单年份',
  PRIMARY KEY (order_id)
) COMMENT '订单表';

INSERT INTO fruit_order(order_id,user_id,user_name,fruit_price,order_year) VALUES
  (1,1011,'张三',13.11,'2019'),
  (4,1011,'张三',22.21,'2020'),
  (6,1011,'张三',58.83,'2020'),
  (2,1022,'李四',23.34,'2019'),
  (3,1022,'李四',12.22,'2019'),
  (7,1022,'李四',14.66,'2021'),
  (8,1022,'李四',34.44,'2021'),
  (5,1033,'王五',51.55,'2020'),
  (9,1033,'王五',63.66,'2021');

单字段分组查询

查询每个客户下单数量，并输出客户 ID 和下单数量。

obclient> SELECT user_id 客户ID, COUNT(order_id) 下单数量 FROM fruit_order GROUP BY user_id;
+----------+--------------+
| 客户ID   | 下单数量      |
+----------+--------------+
|     1011 |            3 |
|     1022 |            4 |
|     1033 |            2 |
+----------+--------------+
3 rows in set

多字段分组查询

查询每个客户每年下单数量，并输出客户 ID 、下单年份和下单数量。

obclient> SELECT user_id 客户ID, order_year 下单年份, COUNT(order_id) 下单数量 FROM fruit_order GROUP BY user_id,order_year;
+----------+--------------+--------------+
| 客户ID   | 下单年份     | 下单数量     |
+----------+--------------+--------------+
|     1011 |         2019 |            1 |
|     1011 |         2020 |            2 |
|     1022 |         2019 |            2 |
|     1022 |         2021 |            2 |
|     1033 |         2020 |            1 |
|     1033 |         2021 |            1 |
+----------+--------------+--------------+
6 rows in set

分组前筛选

查询 2020 年每个客户的下单数量，并输出客户 ID 和下单数量。

obclient> SELECT user_id 客户ID, COUNT(order_id) 下单数量 FROM fruit_order t WHERE t.order_year = 2020 GROUP BY user_id;
+----------+--------------+
| 客户ID   | 下单数量     |
+----------+--------------+
|     1011 |            2 |
|     1033 |            1 |
+----------+--------------+
2 rows in set

分组后筛选

查询 2019 年订单数量大于 1 的客户，并输出客户 ID 和下单数量。

obclient> SELECT user_id 客户ID, COUNT(order_id) 下单数量 FROM fruit_order t WHERE t.order_year = 2019 GROUP BY user_id HAVING COUNT(order_id)>=2;
+----------+--------------+
| 客户ID   | 下单数量     |
+----------+--------------+
|     1022 |            2 |
+----------+--------------+
1 row in set

等价于：

obclient> SELECT user_id 客户ID, COUNT(order_id) 下单数量 FROM fruit_order t WHERE t.order_year = 2019 GROUP BY user_id HAVING 下单数量>=2;
+----------+--------------+
| 客户ID   | 下单数量     |
+----------+--------------+
|     1022 |            2 |
+----------+--------------+
1 row in set

分组后排序

查询每个客户订单的最大金额，然后按照最大金额倒序输出客户 ID 和最大金额。

obclient> SELECT user_id 客户ID, MAX(fruit_price) 最大金额 FROM fruit_order t GROUP BY user_id ORDER BY 最大金额 DESC;
+----------+--------------+
| 客户ID   | 最大金额     |
+----------+--------------+
|     1033 |        63.66 |
|     1011 |        58.83 |
|     1022 |        34.44 |
+----------+--------------+
3 rows in set

欢迎访问 OceanBase 官网获取更多信息：https://www.oceanbase.com/