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

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

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

首先看一下统计的效果图

1、部门人员分析

SELECT
	dept.id id,
	dept.NAME key_name,
	COUNT( pe.id ) value_count,
	dept.deleted 
FROM
	system_dept dept #按部门表查询
	LEFT JOIN system_company_personal pe ON dept.id = pe.dept_id 
WHERE
	dept.id != 100 
	AND dept.STATUS = 0 
	AND dept.deleted = 0 
	AND pe.deleted = 0 
GROUP BY
	dept.id,
	dept.NAME

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

SELECT-- 使用CASE语句进行条件判断，根据不同的工龄范围分类
	CASE
		WHEN 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 
FROM
	system_company_personal 
WHERE
	deleted = 0 
	GROUP BY-- 按照工龄范围分组，以便统计每个范围的人数
		CASE
			WHEN 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、学历分布

SELECT
	CASE
		WHEN 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
FROM
	system_company_personal
where
	education_background_type is not null and deleted = 0
GROUP BY
	education_background_type

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

SELECT
	-- 使用CASE语句进行条件判断，根据不同的年龄范围分类
	CASE
		WHEN 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
FROM
	system_company_personal
where
	birthday IS NOT NULL AND deleted = 0
	GROUP BY
		-- 按照年龄范围分组，以便统计每个范围的人数
		CASE
			WHEN 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
	ORDER BY
	-- 根据年龄范围进行升序排序，这里通过自定义排序规则来实现
	FIELD(key_name, '25以下', '26-30', '31-40', '41-50', '51以上');

5、性别比例

SELECT
	CASE
		WHEN sex = 0 THEN '未知'
		WHEN sex = 1 THEN '男'
		WHEN sex = 2 THEN '女'
	END AS key_name,
	COUNT(*) AS value_count
FROM
	system_company_personal
where
	sex is not null and deleted = 0
GROUP BY sex

6、试用和正式人员

SELECT
	CASE
		WHEN status = 1 THEN '试用'
		WHEN status = 2 THEN '正式'
	END AS key_name,
	COUNT(*) AS value_count
FROM
	system_company_personal
where
	status is not null and deleted = 0 and (status = 1 or status = 2)
GROUP BY status

7、兴趣爱好分析

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

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

好了，就先写到这里吧，有不理解的或者不对的欢迎各位大佬留言指正。