MySQL统计每个月实际在职员工数量

最新推荐文章于 2024-06-06 13:24:55 发布

Westbroobo

最新推荐文章于 2024-06-06 13:24:55 发布

阅读量2.2k

点赞数 2

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/Westbrook_bo/article/details/129324471

版权

文章提供了两种SQL查询方法来统计2020年每个月实际在职员工数量。方法一通过计算每天在职员工数再求每月平均，而方法二则通过笛卡尔积连接员工表和日历表，计算每个员工每月在职天数的平均值。两种方法都考虑了员工的入职和离职时间，确保准确计算在职状态。

摘要由CSDN通过智能技术生成

问题描述

现有如下表和字段：
员工表：employee

字段	说明
employee_id	员工id
employee_name	员工姓名
entry_time	入职时间
leave_office_time	离职时间（若未离职则为null ）

日历表：t_calendar
字段：date

字段	说明
date	日期

需求：写一条SQL查询语句，统计2020年每个月实际在职员工数量，若当月在职天数只有1天，数量计算方式为：1/当月实际天数。

方法一：

统计2020年每天在职员工数再求每月平均

select
 DATE_FORMAT(date,"%Y%m") 月份,
 round(sum(num/day(last_day(date))),2) 月在职员工数
from
(
select date,
count(case when leave_office_time<='2020-01-01' or entry_time>="2020-12-31" then null else employee_id end) as num 
from 
(select date,DATE_FORMAT(date,'%Y%m') months from t_calendar) t1 
left JOIN
(select employee_id,entry_time,leave_office_time from employee) t2
on date >= entry_time and date <= if(leave_office_time is not null,leave_office_time,'2020-12-31')
group by date
)t3
group by DATE_FORMAT(date,"%Y%m")

方法二：

将两表按笛卡尔积的方式全部连接，计算2020年每个员工每月在职实际天数，再相加得到每个月的实际在员工数量。

select 月份,sum(平均在职天数) as 在职人数
from
(
 select employee_id,month(date) as 月份, avg(cnt) as 该月在职实际天数
 from
 (
 select employee_id,employee_name,entry_time,leave_office_time,date,
 if(date between entry_time and leave_office_time,1,0) as cnt
 from
 (select *,
 (case when leave_office_time is null then '2099-12-31' else leave_office_time end) as leave_office_time_fz  from employee) t1,
 (select * from t_calendar where year(date)=2020) t2
 ) t 
 group by employee_id,month(date)
)a
group by 月份
order by 月份