淺談MySQL中的group by

Posted on by

1、前言

MySQL的group by用於對查詢的數據進行分組；此外MySQL提供having子句對分組內的數據進行過濾。

MySQL提供瞭許多select子句關鍵字，

它們在語句中的順序如下所示：

子句 作用 是否必須/何時使用
select 查詢要返回的數據或者表達式 是
from 指定查詢的表 否
where 指定行級過濾 否
group by 分組 否/對數據分組時使用
having 分組過濾 否/對分組後的數據過濾使用
order by 返回數據時指定排序規則 否
limit 指定返回數據的行數 否

2、準備user表

準備一張user表，其DDL和表數據如下所示

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用戶名',
  `nation` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '民族',
  `age` int(11) NULL DEFAULT NULL COMMENT '年齡',
  `height` double NULL DEFAULT NULL COMMENT '身高',
  `sex` smallint(6) NULL DEFAULT NULL COMMENT '性別',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '李子捌', '漢族', 18, 180, 1);
INSERT INTO `user` VALUES (2, '張三', '回族', 20, 175, 1);
INSERT INTO `user` VALUES (3, '李四', '維吾爾族', 45, 168, 0);
INSERT INTO `user` VALUES (4, '王五', '蒙古族', 18, 177, 1);
INSERT INTO `user` VALUES (5, '趙六', '漢族', 16, 184, 0);
INSERT INTO `user` VALUES (6, '田七', '維吾爾族', 27, 192, 1);

user表中數據如下所示：

mysql> select * from user;
+----+--------+----------+------+--------+------+
| id | name   | nation   | age  | height | sex  |
+----+--------+----------+------+--------+------+
|  1 | 李子捌 | 漢族     |   18 |    180 |    1 |
|  2 | 張三   | 回族     |   20 |    175 |    1 |
|  3 | 李四   | 維吾爾族 |   45 |    168 |    0 |
|  4 | 王五   | 蒙古族   |   18 |    177 |    1 |
|  5 | 趙六   | 漢族     |   16 |    184 |    0 |
|  6 | 田七   | 維吾爾族 |   27 |    192 |    1 |
+----+--------+----------+------+--------+------+
6 rows in set (0.00 sec)

2.1 group by規則

使用group by之前需要先瞭解group by使用的相關規則

  • group by子句置於where之後，order by子句之前
  • having 子句置於group by 之後，order by子句之前
  • group by子句中的每個列都必須是select的檢索列或者有效表達式，不能使用聚集函數
  • select中使用的表達式，在group by子句中必須出現，並且不能使用別名
  • group by分組的數據中包含null值，null值被分為一組
  • group by子句可以嵌套，嵌套的分組在最後分組上匯總

2.2 group by使用

需求：

統計不同民族的用戶數

語句：

mysql> select nation, count(*) from user group by nation;
+----------+----------+
| nation   | count(*) |
+----------+----------+
| 漢族     |        2 |
| 回族     |        1 |
| 維吾爾族 |        2 |
| 蒙古族   |        1 |
+----------+----------+
4 rows in set (0.00 sec)

group by可以結合where一起使用，不過where不能在group by之後進行過濾，使用where子句之後，分組的數據是where子句過濾後的數據集。

mysql> select nation, count(*) as nation_num  from user where sex = 0 group by nation;
+----------+------------+
| nation   | nation_num |
+----------+------------+
| 維吾爾族 |          1 |
| 漢族     |          1 |
+----------+------------+
2 rows in set (0.00 sec)

2.3 having使用

對group by分組後的數據還需要再次過濾，就必須使用having子句。group by子句後使用where子句MySQL服務器會拋出異常

mysql> select nation, count(*) as nation_num  from user group by nation where nation = '漢族';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where nation = '漢族'' at line 1

此時隻需要將上面where子句替換成having子句即可，having子句支持所有的where操作符，通俗的說where子句能用的地方隻有替換成having就可以在group by子句後使用瞭

vmysql> select nation, count(*) as nation_num  from user group by nation having nation = '漢族';
+--------+------------+
| nation | nation_num |
+--------+------------+
| 漢族   |          2 |
+--------+------------+
1 row in set (0.00 sec)

2.4 order by與limit

分組後的數據需要排序可以使用order by，order by子句需要更在having子句之後。

mysql> select nation, count(*) as nation_num  from user group by nation having nation != '漢族' order by nation_num desc;
+----------+------------+
| nation   | nation_num |
+----------+------------+
| 維吾爾族 |          2 |
| 回族     |          1 |
| 蒙古族   |          1 |
+----------+------------+
3 rows in set (0.00 sec)

對於輸出的結果需要指定返回的行數，可以使用limit，limit子句在整個語句的最後。

mysql> select nation, count(*) as nation_num  from user group by nation having nation != '漢族' order by nation_num desc limit 2;
+----------+------------+
| nation   | nation_num |
+----------+------------+
| 維吾爾族 |          2 |
| 回族     |          1 |
+----------+------------+
2 rows in set (0.00 sec)

2.5 with rollup

在group by子句中，WITH ROLLUP 可以實現在分組統計數據基礎上再進行相同的統計（SUM,AVG,COUNT…）

比如max()：

mysql> select nation, max(height) as nation_num  from user group by nation with rollup;
+----------+------------+
| nation   | nation_num |
+----------+------------+
| 回族     |        175 |
| 漢族     |        184 |
| 維吾爾族 |        192 |
| 蒙古族   |        177 |
| NULL     |        192 |
+----------+------------+
5 rows in set (0.00 sec)

比如avg()：

mysql> select nation, avg(height) as nation_num  from user group by nation with rollup;
+----------+--------------------+
| nation   | nation_num         |
+----------+--------------------+
| 回族     |                175 |
| 漢族     |                182 |
| 維吾爾族 |                180 |
| 蒙古族   |                177 |
| NULL     | 179.33333333333334 |
+----------+--------------------+
5 rows in set (0.00 sec)

比如count()：

mysql> select nation, count(*) as nation_num  from user group by nation with rollup;
+----------+------------+
| nation   | nation_num |
+----------+------------+
| 回族     |          1 |
| 漢族     |          2 |
| 維吾爾族 |          2 |
| 蒙古族   |          1 |
| NULL     |          6 |
+----------+------------+
5 rows in set (0.00 sec)

到此這篇關於淺談MySQL中的group by的文章就介紹到這瞭,更多相關MySQL中的group by內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：