mysql做数据统计图表常用的sql语句部门人数工龄学历年龄性别在职人员兴趣分析查询

做统计有时候挺头疼的，sql改来改去还是达不到想要的效果，这里分享一下最近写的sql，感兴趣的朋友可以看看了解一下，sql语句代码都有注释。

这里只分享sql查询语句，前端图表用的是Echarts。

首先看一下统计的效果图

在这里插入图片描述

1、部门人员分析

SELECTdept.id id,dept.NAME key_name,COUNT( pe.id ) value_count,dept.deleted 
FROMsystem_dept dept #按部门表查询LEFT JOIN system_company_personal pe ON dept.id = pe.dept_id 
WHEREdept.id != 100 AND dept.STATUS = 0 AND dept.deleted = 0 AND pe.deleted = 0 
GROUP BYdept.id,dept.NAME

2、工龄分布查询（entry_time 为入职时间，格式为datetime）

SELECT-- 使用CASE语句进行条件判断，根据不同的工龄范围分类CASEWHEN DATEDIFF( CURRENT_DATE, entry_time ) / 365 < 1 THEN '1年以下' WHEN DATEDIFF( CURRENT_DATE, entry_time ) / 365 >= 1 AND DATEDIFF( CURRENT_DATE, entry_time ) / 365 < 3 THEN '1-3年'WHEN DATEDIFF( CURRENT_DATE, entry_time ) / 365 >= 3 AND DATEDIFF( CURRENT_DATE, entry_time ) / 365 < 5 THEN '3-5年'WHEN DATEDIFF( CURRENT_DATE, entry_time ) / 365 >= 5 AND DATEDIFF( CURRENT_DATE, entry_time ) / 365 < 10 THEN '5-10年'WHEN DATEDIFF( CURRENT_DATE, entry_time ) / 365 >= 10 AND DATEDIFF( CURRENT_DATE, entry_time ) / 365 < 15 THEN '10-15年'WHEN DATEDIFF( CURRENT_DATE, entry_time ) / 365 >= 15 THEN '15年以上' END AS key_name,COUNT(*) AS value_count 
FROMsystem_company_personal 
WHEREdeleted = 0 GROUP BY-- 按照工龄范围分组，以便统计每个范围的人数CASEWHEN DATEDIFF( CURRENT_DATE, entry_time ) / 365 < 1 THEN '1年以下'WHEN DATEDIFF( CURRENT_DATE, entry_time ) / 365 >= 1 AND DATEDIFF( CURRENT_DATE, entry_time ) / 365 < 3 THEN '1-3年'WHEN DATEDIFF( CURRENT_DATE, entry_time ) / 365 >= 3 AND DATEDIFF( CURRENT_DATE, entry_time ) / 365 < 5 THEN '3-5年'WHEN DATEDIFF( CURRENT_DATE, entry_time ) / 365 >= 5 AND DATEDIFF( CURRENT_DATE, entry_time ) / 365 < 10 THEN '5-10年'WHEN DATEDIFF( CURRENT_DATE, entry_time ) / 365 >= 10 AND DATEDIFF( CURRENT_DATE, entry_time ) / 365 < 15 THEN '10-15年'WHEN DATEDIFF( CURRENT_DATE, entry_time ) / 365 >= 15 THEN '15年以上' END ORDER BY-- 根据工龄范围进行升序排序，这里通过自定义排序规则来实现FIELD( key_name, '1年以下', '1-3年', '3-5年', '5-10年', '10-15年', '15年以上' );

3、学历分布

SELECTCASEWHEN education_background_type = 0  THEN '小学'WHEN education_background_type = 1  THEN '初中'WHEN education_background_type = 2  THEN '高中'WHEN education_background_type = 3  THEN '大专'WHEN education_background_type = 4  THEN '本科'WHEN education_background_type = 5  THEN '硕士'WHEN education_background_type = 6  THEN '博士'END AS key_name,COUNT( * ) value_count
FROMsystem_company_personal
whereeducation_background_type is not null and deleted = 0
GROUP BYeducation_background_type

4、年龄分布（birthday出生日期，格式为datetime）

SELECT-- 使用CASE语句进行条件判断，根据不同的年龄范围分类CASEWHEN TIMESTAMPDIFF(YEAR, birthday, CURRENT_DATE) < 25 THEN '25以下'WHEN TIMESTAMPDIFF(YEAR, birthday, CURRENT_DATE) >= 25 AND TIMESTAMPDIFF(YEAR, birthday, CURRENT_DATE) < 30 THEN '26-30'WHEN TIMESTAMPDIFF(YEAR, birthday, CURRENT_DATE) >= 30 AND TIMESTAMPDIFF(YEAR, birthday, CURRENT_DATE) < 40 THEN '31-40'WHEN TIMESTAMPDIFF(YEAR, birthday, CURRENT_DATE) >= 40 AND TIMESTAMPDIFF(YEAR, birthday, CURRENT_DATE) < 50 THEN '41-50'WHEN TIMESTAMPDIFF(YEAR, birthday, CURRENT_DATE) >= 50 THEN '51以上'END AS key_name,COUNT(*) AS value_count
FROMsystem_company_personal
wherebirthday IS NOT NULL AND deleted = 0GROUP BY-- 按照年龄范围分组，以便统计每个范围的人数CASEWHEN TIMESTAMPDIFF(YEAR, birthday, CURRENT_DATE) < 25 THEN '25以下'WHEN TIMESTAMPDIFF(YEAR, birthday, CURRENT_DATE) >= 25 AND TIMESTAMPDIFF(YEAR, birthday, CURRENT_DATE) < 30 THEN '26-30'WHEN TIMESTAMPDIFF(YEAR, birthday, CURRENT_DATE) >= 30 AND TIMESTAMPDIFF(YEAR, birthday, CURRENT_DATE) < 40 THEN '31-40'WHEN TIMESTAMPDIFF(YEAR, birthday, CURRENT_DATE) >= 40 AND TIMESTAMPDIFF(YEAR, birthday, CURRENT_DATE) < 50 THEN '41-50'WHEN TIMESTAMPDIFF(YEAR, birthday, CURRENT_DATE) >= 50 THEN '51以上'ENDORDER BY-- 根据年龄范围进行升序排序，这里通过自定义排序规则来实现FIELD(key_name, '25以下', '26-30', '31-40', '41-50', '51以上');

5、性别比例

SELECTCASEWHEN sex = 0 THEN '未知'WHEN sex = 1 THEN '男'WHEN sex = 2 THEN '女'END AS key_name,COUNT(*) AS value_count
FROMsystem_company_personal
wheresex is not null and deleted = 0
GROUP BY sex

6、试用和正式人员

SELECTCASEWHEN status = 1 THEN '试用'WHEN status = 2 THEN '正式'END AS key_name,COUNT(*) AS value_count
FROMsystem_company_personal
wherestatus is not null and deleted = 0 and (status = 1 or status = 2)
GROUP BY status

7、兴趣爱好分析

这条 SQL 语句的主要目的是从 system_company_personal 表中提取出每个人的兴趣爱好（存储在 hobbies 字段中，以逗号分隔的字符串形式），将这些兴趣爱好拆分后进行统计，最后找出出现次数大于等于 2 的兴趣爱好及其出现次数，并按照出现次数降序排列。

SELECTkey_name,COUNT(*) AS value_count
FROM (SELECTTRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(hobbies, ',', n.n), ',', -1)) AS key_nameFROMsystem_company_personalJOIN(SELECT 1 AS n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10UNION SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15) nwherehobbies IS NOT NULL and deleted = 0 and (status = 1 or status = 2)AND LENGTH(hobbies) - LENGTH(REPLACE(hobbies, ',', '')) >= n.n - 1
) AS split_hobbiesGROUP BY key_name# HAVING条件可按照自己需求该，我这里是要数量大于2的HAVINGvalue_count >= 2ORDER BYvalue_count DESC;