hello早上好啊！有小伙伴看留言小编说小编发的文章太难了！那么这一篇文章里，就主要讲讲group by的一些技巧，以及子查询。

以下内容都是不能不掌握的。

大家读完文章一定要多练习子查询哦！

本文将用以下数据做演示

这份数据可以看到，这次我们的条数变多了，我们将专门来做Group By操作。

首先来解释以下，什么是group by，什么样的数据会被分组，为什么要被分组。

比如最常见的需求，看看每个门派各有多少人，我们会希望有一份这样的结果：

select 门派, 人数

那我们看看怎么做呢？

首先把下面代码用键盘复制（不要用鼠标）到网页左边，点击Build Schemal

CREATE TABLE `student_info` (

`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id，主键',

`student_name` varchar(50) COMMENT '学生姓名',

`student_age` int COMMENT '学生年龄',

`student_sex` varchar(10) COMMENT '学生性别',

`student_class` varchar(10) COMMENT '门派',

PRIMARY KEY (`id`)

);

INSERT INTO `student_info`

(`student_name`, `student_age`,`student_sex`, `student_class`)

VALUES

('葛尧', 8 , '男', '木叶')

;

INSERT INTO `student_info`

(`student_name`, `student_age`,`student_sex`, `student_class`)

VALUES

('令狐冲', 15 , '男', '华山派')

;

INSERT INTO `student_info`

(`student_name`, `student_age`,`student_sex`, `student_class`)

VALUES

('林平之', 19 , '男', '福威镖局')

;

INSERT INTO `student_info`

(`student_name`, `student_age`,`student_sex`, `student_class`)

VALUES

('岳不群', 40 , '男', '华山派')

;

INSERT INTO `student_info`

(`student_name`, `student_age`,`student_sex`, `student_class`)

VALUES

('林震南', 45 , '男', '福威镖局')

;

INSERT INTO `student_info`

(`student_name`, `student_age`,`student_sex`, `student_class`)

VALUES

('林夫人', 43 , '女', '福威镖局')

;

INSERT INTO `student_info`

(`student_name`, `student_age`,`student_sex`, `student_class`)

VALUES

('小师妹', 14 , '女', '华山派')

;

INSERT INTO `student_info`

(`student_name`, `student_age`,`student_sex`, `student_class`)

VALUES

('仪琳', 18 , '女', '恒山派')

;

INSERT INTO `student_info`

(`student_name`, `student_age`,`student_sex`, `student_class`)

VALUES

('曲菲菲', 13 , '女', '木叶')

;

INSERT INTO `student_info`

(`student_name`, `student_age`,`student_sex`, `student_class`)

VALUES

('任盈盈', 14 , '女', '魔教')

;

用键盘复制到：

SQL Fiddle | A tool for easy online testing and sharing of database problems and their solutions.

​左边，点击Build Shcema

之后，在右边的框框来进行我们的SQL的书写：

SELECT

`student_class` as '门派',

count(1) as '人数'

FROM

`student_info`

GROUP BY

`student_class`

ORDER BY

`人数` DESC

;

点击Run SQL

结果如下：

门派人数统计

ok，结果是对的，但让我们来观察这一段SQL

1，Select的2个值

列`studnet_class`，他出现在了group by语句中，意思是，1个组内所有元素，他们的这一列属性都相同——这10条数据根据各自的`student_class`分成了5组，每个组内元素个数各有不同，但他们都有相同的student_class——因此我们可以select student_class。

第二列是一个count函数，上一次我们说过，count函数是一个聚合函数，他会统计本组内的个数——因此我们可以select count(1)

那如果我们再想select student_sex，可不可以呢？

尽管网页提示我们可以，但实际上在大多数数据库中，是不可以的。会提示你，student_sex 不再 group by字段中，无法select。

为什么？

因为数据仅仅按照student_class分组，那么在每组数据内，只能保证student_class字段相同的，不能保证student_sex是相同的。在这种情况下， MySQL不会允许select这个字段

如果你希望把它select出来，则需要确保每组的student_sex都相同——把student_sex加入group by字段

SELECT

`student_class` as '门派',

count(1) as '人数',

`student_sex`

FROM

`student_info`

GROUP BY

`student_class`,`student_sex`

ORDER BY

`人数` DESC

;

经过分组，会保证每组内的student_class，student_sex都相同，因此组的数量自然会增多，变成下面的样子：

分组的数目由5上升到了8

如果你在select的时候使用了一些函数，比如你希望数据以“木叶_男”（student_class + student_sex），那么你在group by 的时候可以把这两个字段分别group，或者合起来group，如下：

SELECT

concat(`student_class`, '_',`student_sex`) as '门派',

count(1) as '人数'

FROM

`student_info`

GROUP BY

`student_class`,`student_sex`

ORDER BY

`人数` DESC, `门派`

;

这里的concat()函数，是把括号里面的文字拼接起来

也可以把group by写作是：

SELECT

concat(`student_class`, '_',`student_sex`) as '门派',

count(1) as '人数'

FROM

`student_info`

GROUP BY

concat(`student_class`, '_',`student_sex`)

ORDER BY

`人数` DESC, `门派`

;

都是可以的。

只要你可以保证，每个组内被select出来的字段值都是一样的。

结果如下：

分组的概念就介绍到这里

如果你不是很懂，那么多练几次就好了。

2，列名

大家应该有注意到，我总是喜欢select 之后，给他们取一个别名，这个别名被单引号包围（'门派' '人数'），但是在order by字段的时候，他被反引号包围（`门派` `人数`）。

因为他已经是列名了。

而在group by字段里，不能输入别名。

这是为什么呢？

其实这里涉及到MySQL执行顺序，当我们执行一条select语句时，会生成一张临时的结果表。这张表的列名，就是select时的名字。

group by是在表生成之前执行的，而order by是表生成之后执行的。

我们可以对这张结果表进行查询

子查询

先select 出来一张表，然后再这张表上面再做一次查询

你可以无限地嵌套下去

我们来看一个需求：

列出所有的，女性人数小于2的门派

这个需求可以分成2步做

我首先按照门派、性别分组，统计人数

然后过滤——性别是女性，人数小于2

SQL如下：

SELECT

*

FROM

(

SELECT

`student_class` as '门派',

`student_sex`,

count(1) as '人数'

FROM

`student_info`

GROUP BY

`student_class`, `student_sex`

ORDER BY

`人数` DESC, `门派`

) son_result

WHERE

son_result.`student_sex` = '女' and son_result.`人数` < 2

;

双层嵌套的子查询

来看这个SQL，它是2层的，首先看里面这层：

(

SELECT

`student_class` as '门派',

`student_sex`,

count(1) as '人数'

FROM

`student_info`

GROUP BY

`student_class`, `student_sex`

ORDER BY

`人数` DESC, `门派`

) son_result

这一段语句，将会生成一个子表格，叫做son_result，这个表格有3个字段

门派student_sex人数

数据库会先执行这一段SQL，把表格生成完毕，然后再来看外面的SQL，我把它简化成这个样子：

SELECT

*

FROM

son_result

WHERE

son_result.`student_sex` = '女' and son_result.`人数` < 2

;

这样是不是就很明了了？

最后的执行结果如下：

理论上。你可以根据子查询来写无限长的SQL，嵌套无限层子查询，你只需要关注最外面的那个子查询会生成什么样的表结构就行了——完全取决于你的select字段。

更多精彩内容详细了解百战程序员哦！另外喜欢文章请点击关注谢谢啦！小编会提供更多实用的干货给大家的！