统计工龄、学历 年龄 性别 在职人员

SELECT--使用CASE语句进行条件判断，根据不同的工龄范围分类--DATEDIFF算出两个时间相差的天数 CASEWHENDATEDIFF(CURDATE(),create_time)/365<1THEN'1年以下'WHENDATEDIFF(CURDATE(),create_time)/365>=1ANDDATEDIFF(CURDATE(),create_time)/365<3THEN'1-3年'WHENDATEDIFF(CURDATE(),create_time)/365>=3ANDDATEDIFF(CURDATE(),create_time)/365<5THEN'3-5年'WHENDATEDIFF(CURDATE(),create_time)/365>=5ANDDATEDIFF(CURDATE(),create_time)/365<10THEN'5-10年'WHENDATEDIFF(CURDATE(),create_time)/365>=10ANDDATEDIFF(CURDATE(),create_time)/365<15THEN'10-15年'WHENDATEDIFF(CURDATE(),create_time)/365>=15THEN'15年以上'ENDASkey_name,COUNT(*)AS value_count FROM `emp` GROUP BY--按照工龄范围分组，以便统计每个范围的人数 CASEWHENDATEDIFF(CURDATE(),create_time)/365<1THEN'1年以下'WHENDATEDIFF(CURDATE(),create_time)/365>=1ANDDATEDIFF(CURDATE(),create_time)/365<3THEN'1-3年'WHENDATEDIFF(CURDATE(),create_time)/365>=3ANDDATEDIFF(CURDATE(),create_time)/365<5THEN'3-5年'WHENDATEDIFF(CURDATE(),create_time)/365>=5ANDDATEDIFF(CURDATE(),create_time)/365<10THEN'5-10年'WHENDATEDIFF(CURDATE(),create_time)/365>=10ANDDATEDIFF(CURDATE(),create_time)/365<15THEN'10-15年'WHENDATEDIFF(CURDATE(),create_time)/365>=15THEN'15年以上'END ORDER BY--根据工龄范围进行升序排序，这里通过自定义排序规则来实现FIELD(key_name,'1年以下','1-3年','3-5年','5-10年','10-15年','15年以上');

上面的语句也可用下面的方法得到

SELECT CASEWHENTIMESTAMPDIFF(YEAR,create_time,CURRENT_DATE)<1THEN'1年以下'WHENTIMESTAMPDIFF(YEAR,create_time,CURRENT_DATE)>=1ANDTIMESTAMPDIFF(YEAR,create_time,CURRENT_DATE)<3THEN'1-3年'WHENTIMESTAMPDIFF(YEAR,create_time,CURRENT_DATE)>=3ANDTIMESTAMPDIFF(YEAR,create_time,CURRENT_DATE)<5THEN'3-5年'WHENTIMESTAMPDIFF(YEAR,create_time,CURRENT_DATE)>=5ANDTIMESTAMPDIFF(YEAR,create_time,CURRENT_DATE)<10THEN'5-10年'WHENTIMESTAMPDIFF(YEAR,create_time,CURRENT_DATE)>=10ANDTIMESTAMPDIFF(YEAR,create_time,CURRENT_DATE)<15THEN'10-15年'WHENTIMESTAMPDIFF(YEAR,create_time,CURRENT_DATE)>=15THEN'15年以上'ENDASkey_name,COUNT(*)AS value_count FROM `emp` GROUP BY CASEWHENTIMESTAMPDIFF(YEAR,create_time,CURRENT_DATE)<1THEN'1年以下'WHENTIMESTAMPDIFF(YEAR,create_time,CURRENT_DATE)>=1ANDTIMESTAMPDIFF(YEAR,create_time,CURRENT_DATE)<3THEN'1-3年'WHENTIMESTAMPDIFF(YEAR,create_time,CURRENT_DATE)>=3ANDTIMESTAMPDIFF(YEAR,create_time,CURRENT_DATE)<5THEN'3-5年'WHENTIMESTAMPDIFF(YEAR,create_time,CURRENT_DATE)>=5ANDTIMESTAMPDIFF(YEAR,create_time,CURRENT_DATE)<10THEN'5-10年'WHENTIMESTAMPDIFF(YEAR,create_time,CURRENT_DATE)>=10ANDTIMESTAMPDIFF(YEAR,create_time,CURRENT_DATE)<15THEN'10-15年'WHENTIMESTAMPDIFF(YEAR,create_time,CURRENT_DATE)>=15THEN'15年以上'END ORDER BY--根据工龄范围进行升序排序，这里通过自定义排序规则来实现FIELD(key_name,'1年以下','1-3年','3-5年','5-10年','10-15年','15年以上');

SELECT CASEWHENeducation_background_type=0THEN'小学'WHENeducation_background_type=1THEN'初中'WHENeducation_background_type=2THEN'高中'WHENeducation_background_type=3THEN'大专'WHENeducation_background_type=4THEN'本科'WHENeducation_background_type=5THEN'硕士'WHENeducation_background_type=6THEN'博士'--end后的别名,不能与education_background_type相同--相同则报错ENDeducation_background,count(education_background_type)FROM emp GROUP BY CASEWHENeducation_background_type=0THEN'小学'WHENeducation_background_type=1THEN'初中'WHENeducation_background_type=2THEN'高中'WHENeducation_background_type=3THEN'大专'WHENeducation_background_type=4THEN'本科'WHENeducation_background_type=5THEN'硕士'WHENeducation_background_type=6THEN'博士'END--order by field为自定义排序 ORDERBYFIELD(education_background,'小学','初中','高中','大专','本科','硕士','博士')

#对于统计，一般思路是按字段分组,在select上用count、casewhen，字段是数字的考虑sum、avg #TIMESTAMPDIFF用来计算年、月、日、小时、分钟、秒等不同单位的时间差 SELECT CASEWHENTIMESTAMPDIFF(YEAR,birth_day,CURRENT_DATE)<=25THEN'25以下'WHENTIMESTAMPDIFF(YEAR,birth_day,CURRENT_DATE)>25ANDTIMESTAMPDIFF(YEAR,birth_day,CURRENT_DATE)<=30THEN'26-30'WHENTIMESTAMPDIFF(YEAR,birth_day,CURRENT_DATE)>30ANDTIMESTAMPDIFF(YEAR,birth_day,CURRENT_DATE)<=40THEN'31-40'WHENTIMESTAMPDIFF(YEAR,birth_day,CURRENT_DATE)>40ANDTIMESTAMPDIFF(YEAR,birth_day,CURRENT_DATE)<=50THEN'41-50'WHENTIMESTAMPDIFF(YEAR,birth_day,CURRENT_DATE)>50THEN'51以上'ENDbirth_diff,count(birth_day)FROM `emp` WHERE birth_day IS NOT NULL GROUP BY CASEWHENTIMESTAMPDIFF(YEAR,birth_day,CURRENT_DATE)<=25THEN'25以下'WHENTIMESTAMPDIFF(YEAR,birth_day,CURRENT_DATE)>25ANDTIMESTAMPDIFF(YEAR,birth_day,CURRENT_DATE)<=30THEN'26-30'WHENTIMESTAMPDIFF(YEAR,birth_day,CURRENT_DATE)>30ANDTIMESTAMPDIFF(YEAR,birth_day,CURRENT_DATE)<=40THEN'31-40'WHENTIMESTAMPDIFF(YEAR,birth_day,CURRENT_DATE)>40ANDTIMESTAMPDIFF(YEAR,birth_day,CURRENT_DATE)<=50THEN'41-50'WHENTIMESTAMPDIFF(YEAR,birth_day,CURRENT_DATE)>50THEN'51以上'END ORDERBYFIELD(birth_diff,'25以下','26-30','31-40','41-50','51以上')

SELECT CASEWHENtype=1THEN'正式工'WHENtype=0THEN'非正式工'ENDis_type,count(type)FROM emp WHERE type IS NOT NULL GROUP BY type

https://blog.csdn.net/qq_31676725/article/details/143642121