MySQL的数据操作2（GROUP BY、GROUP_CONCAT()）

假设当前数据库记录如下：

CREATE TABLE IF NOT EXISTS mytest(
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '用户编号',
    username VARCHAR(20) NOT NULL UNIQUE COMMENT '用户名',
    sex ENUM('男','女','保密') NOT NULL DEFAULT '保密' COMMENT '性别',
    age TINYINT UNSIGNED NOT NULL COMMENT '年龄'
)ENGINE=INNODB CHARSET=UTF8;



INSERT mytest VALUE(NULL,'king',1,18);
INSERT mytest VALUES(NULL,'Jhon','男',19),(NULL,'Mike',1,20),(NULL,'Jack',1,26);

INSERT mytest VALUE(NULL,'hing','女',18);
INSERT mytest(username,age) VALUE('Mary',17);

INSERT mytest SET username='Black',sex='男',age=19;

INSERT mytest VALUE(DEFAULT,'hung','女',18);

SELECT * FROM mytest;

输出为：

id  username sex age
1   king    男   18
2   Jhon    男   19
3   Mike    男   20
4   Jack    男   26
5   hing    女   18
6   Mary    保密  17
7   Black   男   19
8   hung    女   18

对记录进行分组——GROUP BY与GROUP_CONCAT()

GROUP BY 字段名：根据字段名中记录的不同进行分组
例如：

SELECT id,username,sex FROM mytest
GROUP BY sex;//按照性别的不同进行分组

输出为：

id  username sex
1   king    男
5   hing    女
6   Mary    保密

但是，我们会发现，输出中仅有三个记录，并没有看到其他的记录，因为这里只显示每个组中一条记录，如果要看到组内的记录，要结合GROUP_CONCAT()使用。

输入：

SELECT GROUP_CONCAT(id),GROUP_CONCAT(username),sex FROM mytest
GROUP BY sex;//在分组成功后，列出所有的id和用户名

输出：

group_concat(id)    GROUP_concat(username)      sex
1,2,3,4,7           king,Jhon,Mike,Jack,Black   男
5,8                 hing,hung                   女
6                   Mary                        保密

GROUP BY函数还可以配合其他聚合函数使用：
例如：COUNT()、SUM()、MAX()、MIN()、AVG()
COUNT()统计记录总数（如果写的是COUNT(字段名称)，字段名称的记录中的NULL不计入总数，而COUNT(*)会统计NULL值）
SUM()、MAX()、MIN()、AVG()分别求和、最大值、最小值、平均值

例如：
按照sex分组，得到用户名详情，并且分别统计组中的总人数
输入：

SELECT id,GROUP_CONCAT(username) AS usersDetail,sex, COUNT(*) AS totalusers FROM mytest
GROUP BY sex;

输出

id  usersDetail                 sex  totalusers
1   king,Jhon,Mike,Jack,Black   男   5
5   hing,hung                   女   2
6   Mary                        保密 1

将上面所有的方法一起使用：
输入：

SELECT id,
GROUP_CONCAT(username) AS usersDetail,
sex, 
COUNT(*) AS totalusers,
SUM(age),
MAX(age),
MIN(age),
AVG(age) 
FROM mytest
GROUP BY sex;

输出：

id  usersDetail                sex  totalusers  sum(age)    max(age)    min(age)    avg(age)
1   king,Jhon,Mike,Jack,Black   男      5         102         26           18        20.4000
5   hing,hung                   女      2         36          18           18        18.0000
6   Mary                       保密     1         17          17           17        17.0000

在上面分组的条件下，还可以对得到的分组再进行筛选：
用HAVING字段，输入如下：

SELECT id,GROUP_CONCAT(username) AS usersDetail,sex, COUNT(*) AS totalusers FROM mytest
GROUP BY sex 
HAVING COUNT(*) > 3;//从各个分组中，筛选出分组成员数大于3的分组

或者可以写为；
SELECT id,GROUP_CONCAT(username) AS usersDetail,sex, COUNT(*) AS totalusers FROM mytest
GROUP BY sex 
HAVING  totalusers> 3;//从各个分组中，筛选出分组成员数大于3的分组

输出为：

id  usersDetail                sex  totalusers
1   king,Jhon,Mike,Jack,Black   男   5