分组GROUP BY

ONLY_FULL_GROUP_BY合法性检查

在实际开发迭代中，如果将MySQL数据库版本更新到5.7.5之后，默认开启ONLY_FULL_GROUP_BY模式，导致部分SQL执行报错。例如从win版本上5.7.20-log(此版本可能因为是log长期支持版所以可用)迁移到mac版本5.7.25时，部分项目运行出现异常，暂时无心重构只能将该模式临时关闭。

SELECT @@GLOBAL.sql_mode;
SET @@GLOBAL.sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

默认所有模式： ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

再次运行既没有该错误，还是要趋于大势，趋于规范！

WITH ROLLUP 分组统计的基础上统计

select name,cat,fish,sum(fish) from r group by name,cat,fish with rollup;

区间分组 INTERVAL

INTERVAL(N,N1,N2,N3,N);

INTERVAL()函数进行比较列表(N1，N2，N3等等)中的N值。

该函数如果N<N1返回0； 如果N<N2返回1； 如果N<N3返回2 。

列表值必须是递增的形式才能正常工作。

SELECT INTERVAL ( age, 18, 20, 22 ) AS age_group,
    AVG( grade ) 
FROM student 
GROUP BY age_group;

区间分组 CASE

按照年龄区间统计平均分数

SELECT
    IFNULL ( AVG( CASE WHEN IFNULL( age, 0 ) < 18 THEN grade END ), 0 ) AS "小于18岁",
    IFNULL ( AVG( CASE WHEN IFNULL( age, 0 ) < 20 THEN grade END ), 0 ) AS "18～20岁",
    IFNULL ( AVG( CASE WHEN IFNULL( age, 0 ) < 22 THEN grade END ), 0 ) AS "20～22岁",
    IFNULL ( AVG( CASE WHEN IFNULL( age, 0 )>= 22 THEN grade END ), 0 ) AS "22岁及以上" 
FROM
    student;

分组并过滤

分组计算各年级人数，除了3年级的。

SELECT COUNT( 1 ), grade_num FROM student GROUP BY grade_num HAVING grade_num !=3;

多分组，按照年级，年龄分组，除了年龄大于23岁的。注意HAVING子句字段必须包含在GROUP子句中。

SELECT COUNT( 1 ), grade_num, age FROM student
    GROUP BY grade_num, age HAVING age <23;

-- 也可以对GROUP中多个字段过滤
-- HAVING grade_num !=3 AND age <23;

分组排序

按照年级，再按照分数排序，只取每个年级第一名；但是取其他名次不成功；取前n名时，having字句约束grade_nums <=2(n-1)+1。有问题！

SELECT
    t1.id,
    t1.`name`,
    t1.age,
    t1.sex,
IF
    ( @v_grade = t1.grade, @v_score := @v_score + 1, @v_score := 1 ) AS grade_nums,
    @v_grade := t1.grade AS grade,
    t1.score 
FROM
    ( SELECT * FROM student ORDER BY grade, score DESC ) AS t1,
    ( SELECT @v_grade := NULL, @v_score := NULL ) AS t2 
  HAVING grade_nums =1;