2023年6月27日发(作者：)

SQL聚合统计show tables;create table t_info( pk_id int primary key auto_increment, c_name varchar(100) not null, c_age int)engine=innodb default charset=utf8;insert into t_info values (null, '测试1',22),(null, '测试2',33),(null, '测试3',22),(null, '测试4',52),(null, '测试5',22),(null, '测试6',32),(null, '测试7',26);insert into t_info values (null, '测试11',null);insert into t_info values (null, '123',null);insert into t_info select pk_id+20, c_name, c_age+pk_id from t_info;select * From t_info;-- count 统计数据结果⾏数select count(pk_id) from t_info;select count(*) from t_info;-- 特殊的数字来代表做统计计数select count(3) from t_info;-- select 3 from t_info;-- 强调：⽤某个单独的列作统计时，如果有null值，会被忽略select count(c_age) from t_info;select count(all c_age) from t_info;-- distinct 重复的值会被忽略select count(distinct c_age) from t_info;-- sum 求和（数值列）-- sum后⾯只能跟数值，并且只有1列select sum(c_age) from t_info;-- select sum(c_name) from t_info;-- null值求和，⽤isnull进⾏转换select sum(ifnull(c_age,0)) from t_info where c_age is null;-- avg 求平均（数值列）select avg(c_age) from t_info;-- max/min 求最⼤/最⼩select max(c_age) from t_info;select min(c_age) from t_info;select max(c_name) from t_info;select min(c_name) from t_info;select * from t_info;--- 分组聚合-- 需要统计不同姓名的⼈员的个数-- 查询的投影列⾥有聚合函数出现，并且还需要显⽰⾮聚合的列，就必须要分组-- 分组使⽤专⽤分组⼦句 group by ，同时将⾮聚合的列写在后⾯select c_name 姓名, count(*) as 出现次数 from t_info group by c_name;-- 获取姓名出现次数⼤于1的⼈员个数-- having是聚合后的过滤，专⽤于分组聚合；⽽where分组前的过滤，不参于分组筛选-- 有having就必定有group byselect c_name 姓名, count(*) as 出现次数 from t_info -- where group by c_name having count(*)>1 -- order by ;