淺談MySQL中的group by

更新時間：2021年11月20日 14:42:28 作者：李子捌

這篇文章主要介紹了MySQL中的group by，MySQL的group by用于對查詢的數(shù)據(jù)進行分組；此外MySQL提供having子句對分組內(nèi)的數(shù)據(jù)進行過濾。下面來看看文章對此的具體介紹,需要的朋友可以參考一下，希望對你有所幫助

1、前言

MySQL的group by用于對查詢的數(shù)據(jù)進行分組；此外MySQL提供having子句對分組內(nèi)的數(shù)據(jù)進行過濾。

MySQL提供了許多select子句關(guān)鍵字，

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

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

2、準備user表

準備一張user表，其DDL和表數(shù)據(jù)如下所示

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表中數(shù)據(jù)如下所示：

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規(guī)則

使用group by之前需要先了解group by使用的相關(guān)規(guī)則

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

2.2 group by使用

需求：

統(tǒng)計不同民族的用戶數(shù)

語句：

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

group by可以結(jié)合where一起使用，不過where不能在group by之后進行過濾，使用where子句之后，分組的數(shù)據(jù)是where子句過濾后的數(shù)據(jù)集。

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分組后的數(shù)據(jù)還需要再次過濾，就必須使用having子句。group by子句后使用where子句MySQL服務(wù)器會拋出異常

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

分組后的數(shù)據(jù)需要排序可以使用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)

對于輸出的結(jié)果需要指定返回的行數(shù)，可以使用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 可以實現(xiàn)在分組統(tǒng)計數(shù)據(jù)基礎(chǔ)上再進行相同的統(tǒng)計（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)

到此這篇關(guān)于淺談MySQL中的group by的文章就介紹到這了,更多相關(guān)MySQL中的group by內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: