分组与聚合

一、分组（group by）

    我们查询时根据自己的需要按 特定列 进行分组， 但分组后，  除了特定列的数据是我们需要的直观数据（这也是我们有时用他来代替 distinct()的原因）外，其他列的数据，就是按数据库 自排序显示第一行数据，这并不是我们需要的。 所以我们常常需要用到聚合函数。

说明：

分组的条件：

（1）、 其中某一列

（2）、 表的多列（除了聚合列外， 多列需要显示）

（3）、 根据 表达式 进行分组

eg:

select 
extract(YEAR from cl.`student_birthday` ) as year
,cl.`student_birthday` , cl.`student_name` 
from `clazz_apply` cl where cl.`is_delete` = 0
group by extract(YEAR from cl.`student_birthday` )

二、 聚集函数 

1、 max()  返回集合中的最大值 

2、 min()  返回集合中的最小值

3、 avg() 返回集合中的平均值 

4、 sum() 返回集合中所有值的和

5、count() 返回集合的个数

    如果是对不同值进行计数，  那么需要配合distinct()函数 。

说明：

（1）、聚集函数的参数 ， 可是某一列，或任意表达式。

（2）、count(*)是对所有的行进行统计，包括这一列的行值为Null, 但count()列， 会对null行进行忽略。

eg:

 select max(timestampdiff(year,cl.`student_birthday`,now())) as max_age
    ,min(timestampdiff(year,cl.`student_birthday`,now())) as min_age
    ,avg(timestampdiff(year,cl.`student_birthday`,now()))as avg_age
    ,count(cl.`student_name` ), cl.`clazz_id` 
from `clazz_apply` cl where cl.`is_delete` = 0 group by cl.`clazz_id` ;

三、 显式与隐式的分组

只有聚集函数返回的列， 有时我们不指定分组列， 他会将所有满足条件的行作为一组，进行隐式的分组。

很多时候我们需要根据具体某一列或某几列的值进行分组，  那时我们必须用group by 进行显式的分组。

 

四、 产生合计数

语法： mysql

(1)、 with rollup

场景： 很多时候，我们分组进行了统计， 但最后还需要对  所有的分组  进行合计。使用with rollup就会产生合计数。

详细例子：http://blog.csdn.net/greystar/article/details/48973599

eg:

select 
cl.`status` , extract(YEAR from cl.`student_birthday` ) as year
,cl.`student_birthday` , cl.`student_name` ,count(cl.id)
from `clazz_apply` cl where cl.`is_delete` = 0
group by cl.`status` , extract(YEAR from cl.`student_birthday` ) with rollup 

（2）、with cube (mysql 6.0 以上不支持了)

对于多列分组，他会产生以每一列  作为分组基础而产生的合计。

 

五、 分组的过滤条件

    having 

场景 ：  根据分组聚合后，我们需要聚合后的值进行筛选。（区别与where 子句的不同 ,where 子句是在分组之前被执行的，所以where 只能根据具体列值 与  表达式的值   ）， 但它只能以聚合值作为条件。

eg:

select 
extract(YEAR from cl.`student_birthday`) as year
,count(cl.`id`) 
from `clazz_apply` cl
where cl.`is_delete` = 0
group by  extract(YEAR from cl.`student_birthday` ) 
having count(cl.`id`) < 5

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

转载于:https://my.oschina.net/u/2413865/blog/831940