05-分组

1.group by

• group by的含义:将查询结果按照1个或多个字段进行分组，字段值相同的为一组

• group by可用于单个字段分组，也可用于多个字段分组

    select gender from students group by gender;
    +--------+
    | gender |
    +--------+
    | 男     |
    | 女     |
    | 中性   |
    | 保密   |
    +--------+

    select gender,group_concat(name) from students group by gender;
    +--------+-----------------------------------------------------------+
    | gender | group_concat(name)                                        |
    +--------+-----------------------------------------------------------+
    | 男     | 彭于晏,刘德华,周杰伦,程坤,郭靖                                 |
    | 女     | 小明,小月月,黄蓉,王祖贤,刘亦菲,静香,周杰                        |
    | 中性   | 金星                                                       |
    | 保密   | 凤姐                                                       |
    +--------+-----------------------------------------------------------+


    select gender,group_concat(id) from students group by gender;
    +--------+------------------+
    | gender | group_concat(id) |
    +--------+------------------+
    | 男     | 3,4,8,9,14       |
    | 女     | 1,2,5,7,10,12,13 |
    | 中性   | 11               |
    | 保密   | 6                |
    +--------+------------------+   

    根据gender字段来分组，gender字段的全部值有4个'男','女','中性','保密'，所以分为了4组，  
    当group by单独使用时，只显示出每组的第一条记录, 所以group by单独使用时的实际意义不大

2.group by + group_concat()

• group_concat(字段名)可以作为一个输出字段来使用，

  表示分组之后，根据分组结果，使用group_concat()来放置每一组的某字段的值的集合

  • 特别的group_concat()中可以自定义显示格式：比如group_concat('(id=',id,',name=',name,')')

    select gender from students group by gender;
    +--------+
    | gender |
    +--------+
    | 男     |
    | 女     |
    | 中性   |
    | 保密   |
    +--------+

    select gender,group_concat(name) from students group by gender;
    +--------+-----------------------------------------------------------+
    | gender | group_concat(name)                                        |
    +--------+-----------------------------------------------------------+
    | 男     | 彭于晏,刘德华,周杰伦,程坤,郭靖                                 |
    | 女     | 小明,小月月,黄蓉,王祖贤,刘亦菲,静香,周杰                        |
    | 中性   | 金星                                                       |
    | 保密   | 凤姐                                                       |
    +--------+-----------------------------------------------------------+


    select gender,group_concat(id) from students group by gender;
    +--------+------------------+
    | gender | group_concat(id) |
    +--------+------------------+
    | 男     | 3,4,8,9,14       |
    | 女     | 1,2,5,7,10,12,13 |
    | 中性   | 11               |
    | 保密   | 6                |
    +--------+------------------+

    select gender,group_concat('(id=',id,',name=',name,')') from students where gender!=2 group by gender;
    +--------+----------------------------------------------------------------------------------------------------------+
    | gender | group_concat('(id=',id,',name=',name,')')                                                                |
    +--------+----------------------------------------------------------------------------------------------------------+
    | 男     | (id=3,name=彭于晏),(id=4,name=刘德华),(id=8,name=周杰伦),(id=9,name=程坤),(id=13,name=郭靖)              |
    | 中性   | (id=11,name=金星)                                                                                        |
    | 保密   | (id=6,name=凤姐)                                                                                         |
    +--------+----------------------------------------------------------------------------------------------------------+
    3 rows in set (0.00 sec)

3.group by + 集合函数

• 通过group_concat()的启发，我们既然可以统计出每个分组的某字段的值的集合，

  那么我们也可以通过集合函数来对这个值的集合做一些操作

    select gender,group_concat(age) from students group by gender;
    +--------+----------------------+
    | gender | group_concat(age)    |
    +--------+----------------------+
    | 男     | 29,59,36,27,12       |
    | 女     | 18,18,38,18,25,12,34 |
    | 中性   | 33                   |
    | 保密   | 28                   |
    +--------+----------------------+


    分别统计不同性别的人年龄平均值
    select gender,avg(age) from students group by gender;
    +--------+----------+
    | gender | avg(age) |
    +--------+----------+
    | 男     |  32.6000 |
    | 女     |  23.2857 |
    | 中性   |  33.0000 |
    | 保密   |  28.0000 |
    +--------+----------+

    分别统计不同性别的人的个数
    select gender,count(*) from students group by gender;
    +--------+----------+
    | gender | count(*) |
    +--------+----------+
    | 男     |        5 |
    | 女     |        7 |
    | 中性   |        1 |
    | 保密   |        1 |
    +--------+----------+

4.group by + having

• having 条件表达式：用来分组查询后指定一些条件来输出查询结果

• having作用和where一样，但having只能用于group by

    select gender,count(*) from students group by gender having count(*)>2;
    +--------+----------+
    | gender | count(*) |
    +--------+----------+
    | 男     |        5 |
    | 女     |        7 |
    +--------+----------+

5.group by + with rollup

• with rollup的作用是：在最后新增一行，来记录当前列里所有记录的总和

    select gender,count(*) from students group by gender with rollup;
    +--------+----------+
    | gender | count(*) |
    +--------+----------+
    | 男     |        5 |
    | 女     |        7 |
    | 中性   |        1 |
    | 保密   |        1 |
    | NULL   |       14 |
    +--------+----------+


    select gender,group_concat(age) from students group by gender with rollup;
    +--------+-------------------------------------------+
    | gender | group_concat(age)                         |
    +--------+-------------------------------------------+
    | 男     | 29,59,36,27,12                            |
    | 女     | 18,18,38,18,25,12,34                      |
    | 中性   | 33                                        |
    | 保密   | 28                                        |
    | NULL   | 29,59,36,27,12,18,18,38,18,25,12,34,33,28 |
    +--------+-------------------------------------------+