人效报表，获取同一人每天的处理数据的情况，同时分时段显示

女爷i 2022-05-15 14:17 142阅读 0赞

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d6bjEwNTQxNjIyMjk_size_16_color_FFFFFF_t_70][]

当天任务数：按订单的审核记录表的中任务开始时间统计。当天相同订单多次提交，只计第一次提交；

当天及时完成数=当天任务数中的订单，任务开始时间+24小时>=该订单审核的结束时间 的订单数量；

上班段通过页数：当天订单通过时间在上班时间段内（9:00-18:00）的所有订单的页数总和（一个订单多次通过的，仅计一次通过），下班时间段同理；

上班段驳回页数：当天订单驳回时间在上班时间段内（9:00-18:00）的所有订单的页数总和（驳回时间>第一次通过时间的，不计），下班时间段同理。

这次报表的思路在于：在同一张表“审核表”中取数据时候，先取时间段的数据出来，然后将这六条要求写成相对应的查询语句，再用LEFT JOIN（使用右外连接应该也可以，本人没有试过）连接起来取每一天的数据，最后把整个连接表当做一个新表进行查询。这样子查询出来的数据就是一张列表，在mybatis中使用map类型返回值，在控制器中用list封装，即可读取出数据。

（还有第二种思路：先取出每一天的数据，再取一个时间段的数据，即先部分后整体。但是这样子要使用oracle循环，写存储过程，比较繁琐，有兴趣的朋友可以尝试一下）

以下是具体的SQL语句：

SELECT CU.NAME,  
             A\_TIME,   
             NVL(COUNT(TASK\_COUNT),0) TASK\_COUNT,   
              NVL(COUNT(COMPLETE\_TASK\_COUNT),0) COMPLETE\_TASK\_COUNT,   
             NVL(SUM(PASS\_COUNT\_WORK),0) PASS\_COUNT\_WORK,   
             NVL(SUM(PASS\_COUNT\_NO\_WORK),0) PASS\_COUNT\_NO\_WORK,   
             NVL(SUM(REJECT\_COUNT\_WORK),0) REJECT\_COUNT\_WORK,   
             NVL(SUM(REJECT\_COUNT\_NO\_WORK),0) REJECT\_COUNT\_NO\_WORK,

NVL(SUM(PASS\_COUNT\_WORK),0)+NVL(SUM(PASS\_COUNT\_NO\_WORK),0) PASS\_WORK,  
             NVL(SUM(REJECT\_COUNT\_WORK),0)+NVL(SUM(REJECT\_COUNT\_NO\_WORK),0) REJECT\_WORK,  
             NVL(SUM(PASS\_COUNT\_WORK),0)+NVL(SUM(PASS\_COUNT\_NO\_WORK),0)+NVL(SUM(REJECT\_COUNT\_WORK),0)+NVL(SUM(REJECT\_COUNT\_NO\_WORK),0) ALL\_COUNT,

ROUND(NVL(COUNT(COMPLETE\_TASK\_COUNT),0)/NVL(COUNT(TASK\_COUNT),0),3) COMPLETE\_EFFICIENCY,  
             ROUND((NVL(SUM(PASS\_COUNT\_WORK),0)+NVL(SUM(PASS\_COUNT\_NO\_WORK),0))/(NVL(SUM(PASS\_COUNT\_WORK),0)+NVL(SUM(PASS\_COUNT\_NO\_WORK),0)+NVL(SUM(REJECT\_COUNT\_WORK),0)+NVL(SUM(REJECT\_COUNT\_NO\_WORK),0)),3) PASS\_EFFICIENCY  
        FROM  
        (  
        SELECT A.A\_ORDER\_ID, A.RECEIVING\_LERK\_ID, A.A\_TIME, A.TASK\_COUNT,   
                     B.COMPLETE\_TASK\_COUNT,  
                     C.PASS\_COUNT\_WORK,  
                     D.PASS\_COUNT\_NO\_WORK,  
                     E.REJECT\_COUNT\_WORK,  
                     F.REJECT\_COUNT\_NO\_WORK  
        FROM  
        (//当天任务数  
            select count(0) TASK\_COUNT,to\_char(TASK\_START\_TIME,'yyyy-MM-dd') A\_TIME,t1.RECEIVING\_LERK\_ID ,T1.ORDER\_ID A\_ORDER\_ID  
            from CRM\_ORDER\_AUDIT t1   
            where t1.TASK\_START\_TIME >= to\_date(\#\{startDate\},'yyyy-MM-dd')   
            and t1.TASK\_START\_TIME <=  to\_date(\#\{endDate\},'yyyy-MM-dd')   
            AND t1.AUDIT\_COUNT = 1  
            GROUP BY to\_char(T1.TASK\_START\_TIME,'yyyy-MM-dd'),t1.RECEIVING\_LERK\_ID,T1.ORDER\_ID  
        ) A  
        LEFT JOIN  
        (//当天及时完成数  
            SELECT COUNT(C.ID) COMPLETE\_TASK\_COUNT,C.RECEIVING\_LERK\_ID,C.ORDER\_ID B\_ORDER\_ID  
            from CRM\_ORDER\_AUDIT C  
            where   
            C.TASK\_START\_TIME >= to\_date(\#\{startDate\},'yyyy-MM-dd')   
            and C.TASK\_START\_TIME <=  to\_date(\#\{endDate\},'yyyy-MM-dd')   
            AND (C.TASK\_START\_TIME < C.END\_TIME)  
            AND ((to\_char(C.END\_TIME,'yyyy-mm-dd HH24:MI:SS')) < to\_char(C.TASK\_START\_TIME+1,'yyyy-mm-dd HH24:MI:SS'))  
            AND C.AUDIT\_COUNT = 1  
            GROUP BY C.RECEIVING\_LERK\_ID,C.ORDER\_ID  
        ) B ON (A.RECEIVING\_LERK\_ID = B.RECEIVING\_LERK\_ID AND A.A\_ORDER\_ID = B.B\_ORDER\_ID)  
        LEFT JOIN  
        (//上班段通过页数  
            SELECT SUM(A.TOTAL\_COUNT) PASS\_COUNT\_WORK, RECEIVING\_LERK\_ID, ORDER\_ID C\_ORDER\_ID  
            FROM CRM\_ORDER\_AUDIT A   
            WHERE A.END\_TIME BETWEEN to\_date(\#\{startDate\},'yyyy-MM-dd') AND to\_date(\#\{endDate\},'yyyy-MM-dd')  
            AND A.TYPE = 1  
            AND (to\_char(A.END\_TIME,'HH24') >= 09)  
            AND (to\_char(A.END\_TIME,'HH24') <= 18)  
            AND A.AUDIT\_COUNT IN (  
                SELECT MIN(B.AUDIT\_COUNT) FROM CRM\_ORDER\_AUDIT B   
                WHERE B.END\_TIME BETWEEN to\_date(\#\{startDate\},'yyyy-MM-dd') AND to\_date(\#\{endDate\},'yyyy-MM-dd')  
                AND B.TYPE = 1  
                AND A.ORDER\_ID = B.ORDER\_ID  
                AND (to\_char(B.END\_TIME,'HH24') >= 09)  
                AND (to\_char(B.END\_TIME,'HH24') <= 18)  
            )  
            GROUP BY RECEIVING\_LERK\_ID,ORDER\_ID  
        ) C ON (A.RECEIVING\_LERK\_ID = C.RECEIVING\_LERK\_ID AND A.A\_ORDER\_ID = C.C\_ORDER\_ID)  
        LEFT JOIN  
        (//下班段通过页数  
            SELECT SUM(A.TOTAL\_COUNT) PASS\_COUNT\_NO\_WORK, RECEIVING\_LERK\_ID, ORDER\_ID D\_ORDER\_ID  
            FROM CRM\_ORDER\_AUDIT A   
            WHERE A.END\_TIME BETWEEN to\_date(\#\{startDate\},'yyyy-MM-dd') AND to\_date(\#\{endDate\},'yyyy-MM-dd')  
            AND A.TYPE = 1  
            AND (to\_char(A.END\_TIME,'HH24') >= 00 AND to\_char(A.END\_TIME,'HH24') < 09)  
            OR (to\_char(A.END\_TIME,'HH24') > 18 AND to\_char(A.END\_TIME,'HH24') < 24)  
            AND A.AUDIT\_COUNT IN (  
                SELECT MIN(B.AUDIT\_COUNT) FROM CRM\_ORDER\_AUDIT B   
                WHERE B.END\_TIME BETWEEN to\_date(\#\{startDate\},'yyyy-MM-dd') AND to\_date(\#\{endDate\},'yyyy-MM-dd')  
                AND B.TYPE = 1  
                AND A.ORDER\_ID = B.ORDER\_ID  
                AND (to\_char(B.END\_TIME,'HH24') >= 00 AND to\_char(B.END\_TIME,'HH24') < 09)  
                OR (to\_char(B.END\_TIME,'HH24') > 18 AND to\_char(B.END\_TIME,'HH24') < 24)  
            )  
            GROUP BY RECEIVING\_LERK\_ID,ORDER\_ID  
        ) D ON (A.RECEIVING\_LERK\_ID = D.RECEIVING\_LERK\_ID AND A.A\_ORDER\_ID = D.D\_ORDER\_ID)  
        LEFT JOIN  
        (//上班段驳回页数  
            SELECT SUM(A.REJECT\_COUNT) REJECT\_COUNT\_WORK, RECEIVING\_LERK\_ID, ORDER\_ID E\_ORDER\_ID  
            FROM CRM\_ORDER\_AUDIT A   
            WHERE A.END\_TIME BETWEEN to\_date(\#\{startDate\},'yyyy-MM-dd') AND to\_date(\#\{endDate\},'yyyy-MM-dd')  
            AND A.TYPE = 0  
            AND (to\_char(A.END\_TIME,'HH24') >= 09)  
            AND (to\_char(A.END\_TIME,'HH24') <= 18)  
            AND A.AUDIT\_COUNT IN (  
                SELECT B.AUDIT\_COUNT FROM CRM\_ORDER\_AUDIT B   
                WHERE B.END\_TIME BETWEEN to\_date(\#\{startDate\},'yyyy-MM-dd') AND to\_date(\#\{endDate\},'yyyy-MM-dd')  
                AND B.TYPE = 0  
                AND A.ORDER\_ID = B.ORDER\_ID  
                AND (to\_char(B.END\_TIME,'HH24') >= 09)  
                AND (to\_char(B.END\_TIME,'HH24') <= 18)  
                AND B.END\_TIME <   
                (  
                    SELECT C.END\_TIME  
                    FROM CRM\_ORDER\_AUDIT C  
                    WHERE C.TYPE = 1 AND C.ID = (  
                        SELECT MIN(D.ID) FROM CRM\_ORDER\_AUDIT D WHERE D.TYPE = 1 AND D.END\_TIME BETWEEN to\_date(\#\{startDate\},'yyyy-MM-dd') AND to\_date(\#\{endDate\},'yyyy-MM-dd') AND B.ORDER\_ID = D.ORDER\_ID  
                    )  
                )  
            )  
            GROUP BY RECEIVING\_LERK\_ID,ORDER\_ID  
        ) E ON (A.RECEIVING\_LERK\_ID = E.RECEIVING\_LERK\_ID AND A.A\_ORDER\_ID = E.E\_ORDER\_ID)  
        LEFT JOIN  
        (//下班段驳回页数  
            SELECT SUM(A.REJECT\_COUNT) REJECT\_COUNT\_NO\_WORK, RECEIVING\_LERK\_ID, ORDER\_ID F\_ORDER\_ID  
            FROM CRM\_ORDER\_AUDIT A   
            WHERE A.END\_TIME BETWEEN to\_date(\#\{startDate\},'yyyy-MM-dd') AND to\_date(\#\{endDate\},'yyyy-MM-dd')  
            AND A.TYPE = 0  
            AND (to\_char(A.END\_TIME,'HH24') >= 00 AND to\_char(A.END\_TIME,'HH24') < 09)  
            OR (to\_char(A.END\_TIME,'HH24') > 18 AND to\_char(A.END\_TIME,'HH24') < 24)  
            AND A.AUDIT\_COUNT IN (  
                SELECT B.AUDIT\_COUNT FROM CRM\_ORDER\_AUDIT B   
                WHERE B.END\_TIME BETWEEN to\_date(\#\{startDate\},'yyyy-MM-dd') AND to\_date(\#\{endDate\},'yyyy-MM-dd')  
                AND B.TYPE = 0  
                AND A.ORDER\_ID = B.ORDER\_ID  
                AND (to\_char(A.END\_TIME,'HH24') >= 00 AND to\_char(A.END\_TIME,'HH24') < 09)  
                OR (to\_char(A.END\_TIME,'HH24') > 18 AND to\_char(A.END\_TIME,'HH24') < 24)  
                AND B.END\_TIME <   
                (  
                    SELECT C.END\_TIME  
                    FROM CRM\_ORDER\_AUDIT C  
                    WHERE C.TYPE = 1 AND C.ID = (  
                        SELECT MIN(D.ID) FROM CRM\_ORDER\_AUDIT D WHERE D.TYPE = 1 AND D.END\_TIME BETWEEN to\_date(\#\{startDate\},'yyyy-MM-dd') AND to\_date(\#\{endDate\},'yyyy-MM-dd') AND B.ORDER\_ID = D.ORDER\_ID  
                    )  
                )  
            )  
            GROUP BY RECEIVING\_LERK\_ID,ORDER\_ID  
        ) F ON (A.RECEIVING\_LERK\_ID = F.RECEIVING\_LERK\_ID AND A.A\_ORDER\_ID = F.F\_ORDER\_ID)  
          
        GROUP BY A.A\_ORDER\_ID, A.TASK\_COUNT, A.A\_TIME, A.RECEIVING\_LERK\_ID,  
                         B.COMPLETE\_TASK\_COUNT,  
                         C.PASS\_COUNT\_WORK,  
                         D.PASS\_COUNT\_NO\_WORK,  
                         E.REJECT\_COUNT\_WORK,  
                         F.REJECT\_COUNT\_NO\_WORK  
        ORDER BY A.A\_TIME DESC  
        ) combination   
        LEFT JOIN CRM\_USER CU ON CU.ID = RECEIVING\_LERK\_ID  
         
        GROUP BY A\_TIME,CU.NAME  
        ORDER BY A\_TIME DESC

注：一下子看不懂的亲们，可以先将六条select查询拿出来看，是可以单独运行的。本人仅提供思路，因此不会提供“审核表”的具体参数说明。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d6bjEwNTQxNjIyMjk_size_16_color_FFFFFF_t_70]: /images/20220503/e96ce7451fdb4507a494a0f76dd2d016.png