GROUP BY 练习题答案
基础分组查询
统计每个部门的员工数量
SELECT e_department, COUNT(*) as 员工数量
FROM employees
GROUP BY e_department;统计不同性别和学历组合的员工数量
SELECT e_gender, e_education, COUNT(*) as 人数
FROM employees
GROUP BY e_gender, e_education;按邮箱域名分组统计员工数量
SELECT
SUBSTRING(e_email FROM POSITION('@' IN e_email)+1) as 邮箱域名,
COUNT(*) as 人数
FROM employees
GROUP BY 邮箱域名;统计每个省份的员工数量
SELECT e_province, COUNT(*) as 员工数量
FROM employees
GROUP BY e_province;比较运算符应用
统计每个部门中薪资大于10000元的员工数量
SELECT e_department, COUNT(*) as 高薪人数
FROM employees
WHERE e_salary > 10000
GROUP BY e_department;将员工按年龄分段统计（30岁以下，30-40岁，40岁以上）
SELECT
CASE
WHEN e_age < 30 THEN '30岁以下'
WHEN e_age BETWEEN 30 AND 40 THEN '30-40岁'
ELSE '40岁以上'
END as 年龄段,
COUNT(*) as 人数
FROM employees
GROUP BY 年龄段;统计不同入职年限段的员工数量（2020年以前和以后）
SELECT
CASE
WHEN e_hiredate < '2020-01-01' THEN '2020年前入职'
ELSE '2020年及以后入职'
END as 入职时间段,
COUNT(*) as 人数
FROM employees
GROUP BY 入职时间段;统计每个部门中年龄大于35岁的员工数量
SELECT e_department, COUNT(*) as 人数
FROM employees
WHERE e_age > 35
GROUP BY e_department;逻辑运算符应用
统计每个部门中年龄在25-35岁且学历为本科或研究生的员工数量
SELECT e_department, COUNT(*) as 人数
FROM employees
WHERE e_age BETWEEN 25 AND 35 AND (e_education = '本科' OR e_education = '研究生')
GROUP BY e_department;统计薪资在8000-12000元之间且不是后勤部的员工按部门分组的人数
SELECT e_department, COUNT(*) as 人数
FROM employees
WHERE e_salary BETWEEN 8000 AND 12000 AND e_department != '后勤部'
GROUP BY e_department;统计每个部门中女性员工且薪资大于9000元的人数
SELECT e_department, COUNT(*) as 人数
FROM employees
WHERE e_gender = '女' AND e_salary > 9000
GROUP BY e_department;统计年龄小于30岁或大于40岁的员工按学历分组的人数
SELECT e_education, COUNT(*) as 人数
FROM employees
WHERE e_age < 30 OR e_age > 40
GROUP BY e_education;包含运算符(IN)应用
统计来自河北、河南、山东三省员工按省份分组的人数
SELECT e_province, COUNT(*) as 人数
FROM employees
WHERE e_province IN ('河北', '河南', '山东')
GROUP BY e_province;统计职位包含"总监"或"主管"的员工按部门分组的人数
SELECT e_department, COUNT(*) as 人数
FROM employees
WHERE e_job IN ('技术总监', '市场总监', '财务总监', '人力总监', '行政主管')
GROUP BY e_department;统计在技术部、市场部、财务部工作的员工按部门分组的人数
SELECT e_department, COUNT(*) as 人数
FROM employees
WHERE e_department IN ('技术部', '市场部', '财务部')
GROUP BY e_department;统计来自郑州、济南、太原三个城市员工按城市分组的人数
SELECT e_city, COUNT(*) as 人数
FROM employees
WHERE e_city IN ('郑州', '济南', '太原')
GROUP BY e_city;匹配运算符(LIKE)应用
统计职位名称以"员"结尾的员工按部门分组的人数
SELECT e_department, COUNT(*) as 人数
FROM employees
WHERE e_job LIKE '%员'
GROUP BY e_department;统计姓氏为"王"或"张"的员工按姓氏分组的人数
SELECT
CASE
WHEN e_name LIKE '王%' THEN '王'
WHEN e_name LIKE '张%' THEN '张'
ELSE '其他'
END as 姓氏,
COUNT(*) as 人数
FROM employees
GROUP BY 姓氏;统计职位名称中包含"技术"或"设计"的员工按部门分组的人数
SELECT e_department, COUNT(*) as 人数
FROM employees
WHERE e_job LIKE '%技术%' OR e_job LIKE '%设计%'
GROUP BY e_department;统计使用QQ邮箱或163邮箱的员工按邮箱类型分组的人数
SELECT
CASE
WHEN e_email LIKE '%qq.com%' THEN 'QQ邮箱'
WHEN e_email LIKE '%163.com%' THEN '163邮箱'
ELSE '其他邮箱'
END as 邮箱类型,
COUNT(*) as 人数
FROM employees
GROUP BY 邮箱类型;组合查询应用
统计每个部门中研究生学历且年龄小于35岁的员工数量
SELECT e_department, COUNT(*) as 人数
FROM employees
WHERE e_education = '研究生' AND e_age < 35
GROUP BY e_department;统计2018-2020年期间入职的员工按年份分组的人数
SELECT
EXTRACT(YEAR FROM e_hiredate) as 入职年份,
COUNT(*) as 人数
FROM employees
WHERE e_hiredate BETWEEN '2018-01-01' AND '2020-12-31'
GROUP BY 入职年份;将员工按薪资等级分组统计（<8000, 8000-12000, >12000）
SELECT
CASE
WHEN e_salary < 8000 THEN '低薪'
WHEN e_salary BETWEEN 8000 AND 12000 THEN '中薪'
ELSE '高薪'
END as 薪资等级,
COUNT(*) as 人数
FROM employees
GROUP BY 薪资等级;统计来自省会城市且薪资大于10000元的员工按城市分组的人数
SELECT e_city, COUNT(*) as 人数
FROM employees
WHERE e_city IN ('郑州', '济南', '太原') AND e_salary > 10000
GROUP BY e_city;复杂条件组合
统计30-40岁且学历为研究生的员工按性别分组的人数
SELECT e_gender, COUNT(*) as 人数
FROM employees
WHERE e_age BETWEEN 30 AND 40 AND e_education = '研究生'
GROUP BY e_gender;统计使用不同邮箱类型的员工按部门和邮箱类型分组的人数
SELECT
e_department,
CASE
WHEN e_email LIKE '%163.com%' THEN '163邮箱'
WHEN e_email LIKE '%qq.com%' THEN 'QQ邮箱'
WHEN e_email LIKE '%126.com%' THEN '126邮箱'
WHEN e_email LIKE '%sohu.com%' THEN '搜狐邮箱'
ELSE '其他邮箱'
END as 邮箱类型,
COUNT(*) as 人数
FROM employees
GROUP BY e_department, 邮箱类型;统计来自非省会城市且年龄大于35岁或薪资低于8000元的员工按省份分组的人数
SELECT e_province, COUNT(*) as 人数
FROM employees
WHERE e_city NOT IN ('郑州', '济南', '太原')
AND (e_age > 35 OR e_salary < 8000)
GROUP BY e_province;统计姓名长度为2个字（单姓）且年龄小于30岁的员工按部门分组的人数
SELECT e_department, COUNT(*) as 人数
FROM employees
WHERE LENGTH(e_name) = 2 AND e_age < 30
GROUP BY e_department;这些SQL查询涵盖了GROUP BY的各种用法，包括：
单字段分组
多字段分组
与WHERE子句结合使用
使用CASE WHEN进行条件分组
结合字符串函数进行分组
各种运算符在分组查询中的应用