统计图数据集（增员-减员-累计总和）

一、 月度统计

1.sql案例及参数

{startDate} 开始统计时间 范例：2021-08-01

{endDate} 结束统计时间 范例：2022-07-01

SELECT 
  t1.datetime, 
  IFNULL(t2.num, 0) AS recruiting,
  IFNULL(t4.num, 0) AS downsizing,
  (@var := @var + IFNULL(t2.num, 0)) as total
FROM (
        SELECT
        @a :=@a + 1 AS `index`,
        DATE_FORMAT((#{endDate} - INTERVAL @a MONTH), '%Y-%m') AS datetime
        FROM
        mysql.help_topic,(SELECT @a:=-1) temp
        WHERE
        @a <![CDATA[<]]> (SELECT TIMESTAMPDIFF(MONTH,#{startDate},DATE_FORMAT(#{endDate}, '%Y-%m-%d')))
        ) t1
LEFT JOIN (
        SELECT DATE_FORMAT(t3.`create_date`, '%Y-%m') AS datetime, count(1) num
        FROM '表名' t3
                -- 在职人员
        where t3.user_status = '0'
        GROUP BY DATE_FORMAT(t3.`create_date`, '%Y-%m')
) t2 ON t1.datetime = t2.datetime
LEFT JOIN (
        SELECT DATE_FORMAT(t5.`create_date`, '%Y-%m') AS datetime, count(1) num
        FROM '表名' t5
                -- 离职人员
        where t5.user_status != '0'
        GROUP BY DATE_FORMAT(t5.`create_date`, '%Y-%m')
) t4 ON t1.datetime = t4.datetime
INNER JOIN (
        SELECT @var:=(SELECT count(1) from t_user_info where user_status = '0' and create_date <![CDATA[<]]> #{startDate}) )T
ORDER BY t1.datetime ASC

2.返回数据

月统计

二、 日统计

1.sql案例及参数

{startDate} 开始统计时间 范例：2022-01-01

{endDate} 结束统计时间 范例：2022-01-31

SELECT 
    t1.datetime, 
    IFNULL(t2.num, 0) AS recruiting,
    IFNULL(t4.num, 0) AS downsizing,
    (@var := @var + IFNULL(t2.num, 0)) as total
FROM (
        SELECT
        @a :=@a + 1 AS `index`,
        DATE_FORMAT((#{endDate} - INTERVAL @a DAY), '%Y-%m-%d') AS datetime
        FROM
        mysql.help_topic,(SELECT @a:=-1) temp
        WHERE
        @a <![CDATA[<]]> (YEAR(#{endDate})-YEAR(#{startDate}))*365 + (MONTH(#{endDate})- MONTH(#{startDate}))*30  + (DAY(#{endDate})- DAY(#{startDate}))
) t1
LEFT JOIN (
        SELECT 
            DATE_FORMAT(t3.`create_date`, '%Y-%m-%d') AS datetime, 
            count(1) num
        FROM t_user_info t3
        where t3.user_status = '0'
        GROUP BY DATE_FORMAT(t3.`create_date`, '%Y-%m-%d')
) t2 ON t1.datetime = t2.datetime
LEFT JOIN (
        SELECT 
            DATE_FORMAT(t5.`create_date`, '%Y-%m-%d') AS datetime, 
            count(1) num
        FROM t_user_info t5
        where t5.user_status != '0'
        GROUP BY DATE_FORMAT(t5.`create_date`, '%Y-%m-%d')
) t4 ON t1.datetime = t4.datetime
INNER JOIN (
        SELECT @var:=(SELECT count(1) from t_user_info where user_status = '0' and create_date <![CDATA[<]]> #{startDate}) 
)T
ORDER BY t1.datetime ASC

2.返回数据

日统计
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
【社区内容提示】社区部分内容疑似由AI辅助生成，浏览时请结合常识与多方信息审慎甄别。
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

相关阅读更多精彩内容

友情链接更多精彩内容