月份以及对应的入职人数

最新推荐文章于 2024-07-16 10:28:31 发布

删除没备份的痛

最新推荐文章于 2024-07-16 10:28:31 发布

阅读量35

点赞数 1

文章标签：数据库 sql mysql

本文链接：https://blog.csdn.net/qq_41995845/article/details/140455223

版权

WITH RECURSIVE Months AS (  
    SELECT '2023-01-25' AS month_start  
    UNION ALL  
    SELECT DATE_ADD(month_start, INTERVAL 1 MONTH)  
    FROM Months  
    WHERE month_start < '2023-12-01'  
),  
FormattedMonths AS (  
    SELECT DATE_FORMAT(month_start, '%Y-%m') AS month  
    FROM Months  
)  ,
RankedRosters AS (
        SELECT
        id,
        roster_id,
        entry_date,
        sync_date,
        ROW_NUMBER() OVER(PARTITION BY roster_id ORDER BY sync_date DESC, id DESC) AS rn
        FROM
        hr_his_roster
        )
SELECT  
    fm.month,  
    COALESCE(COUNT(hhr.entry_date), 0) AS 入职人数  
FROM  
    FormattedMonths fm  
LEFT JOIN RankedRosters hhr ON DATE_FORMAT(hhr.entry_date, '%Y-%m') = fm.month  
where hhr.rn=1
GROUP BY  
    fm.month  
ORDER BY  
    fm.month;