一、分组查询
1、根据性别分组,统计男性员工 和 女性员工 的数量
-- step1. 先根据性别分组 group by gender
select * from emp group by gender;
-- step2. 分组后统计数量 count(*)
select count(*) from emp group by gender;
-- step3. 分清男性员工多少个,女性员工多少个
select gender, count(*) from emp group by gender;
2、根据性别分组,统计男性员工 和 女性员工 的平均年龄
select gender, avg(age) from emp group by gender;
3、查询年龄小于45的员工,并根据工作地址分组,获取员工数量大于等于3的工作地址
方法一:
方法二:
【代码】
-- 分组查询
-- 1、根据性别分组,统计男性员工 和 女性员工 的数量
-- step1. 先根据性别分组 group by gender
select * from emp group by gender;
-- step2. 分组后统计数量 count(*)
select count(*) from emp group by gender;
-- step3. 分清男性员工多少个,女性员工多少个
select gender, count(*) from emp group by gender;
-- 2、根据性别分组,统计男性员工 和 女性员工 的平均年龄
select gender, avg(age) from emp group by gender;
-- 3、查询年龄小于45的员工,并根据工作地址分组,获取员工数量大于等于4的工作地址
-- step1. 选出年龄小于45的员工
select * from emp where age < 45;
-- step2. 根据工作地址分组
select * from emp where age < 45 group by workaddress;
-- step3. 选出员工数量大于等于4的分组
select * from emp where age < 45 group by workaddress having count(*) >= 4;
-- step4. 获取分组的工作地址
select workaddress from emp where age < 45 group by workaddress having count(*) >= 4;
-- step1. 选出年龄小于45的员工
select * from emp where age < 45;
-- step2. 根据工作地址分组
select * from emp where age < 45 group by workaddress;
-- step3. 获取每个分组的人数
select count(*) from emp where age < 45 group by workaddress;
-- step4. 方便看出是哪个工作地址
select workaddress, count(*) from emp where age < 45 group by workaddress;
-- step5. 获取人数大于等于4的工作地址(分组后再过滤,用having)
select workaddress, count(*) from emp where age < 45 group by workaddress having count(*) >= 4;
-- step6. 可以对人数取个别名
select workaddress, count(*) address_count from emp where age < 45 group by workaddress having address_count >= 4;