nxybcce
2017-03-11 03:28
采纳率: 100%
浏览 3.9k

求一考勤统计实现SQL代码

目前有两张表如下

 --用户当日班次表
IF OBJECT_ID('[userKQ]') IS NOT NULL
    DROP TABLE userKQ

CREATE TABLE userKQ
(
dateKQ VARCHAR (10),  --日期
userid VARCHAR(20),   --员工ID
banci VARCHAR(20),    --班次名称
StartTime DATETIME,   --上班时间
EndTime DATETIME,     --下班时间
StartTime1 DATETIME,  --上班有效打卡时间开始
StartTime2 DATETIME,  --上班有效打卡时间结束
EndTime1 DATETIME,    --下班有效打卡时间开始
EndTime2 DATETIME     --下班有效打卡时间结束
)

INSERT INTO userKQ
SELECT '2017-03-02','000015','上午','1900-01-01 08:00:00.000','1900-01-01 12:00:00.000','1900-01-01 07:30:00.000','1900-01-01 08:30:00.000','1900-01-01 11:30:00.000','1900-01-01 12:30:00.000' UNION ALL
SELECT '2017-03-02','000015','下午','1900-01-01 14:00:00.000','1900-01-01 18:00:00.000','1900-01-01 13:30:00.000','1900-01-01 14:30:00.000','1900-01-01 17:30:00.000','1900-01-01 18:30:00.000' UNION ALL
SELECT '2017-03-02','000002','上午','1900-01-01 08:00:00.000','1900-01-01 12:00:00.000','1900-01-01 07:30:00.000','1900-01-01 08:30:00.000','1900-01-01 11:30:00.000','1900-01-01 12:30:00.000' UNION ALL
SELECT '2017-03-02','000002','下午','1900-01-01 14:00:00.000','1900-01-01 18:00:00.000','1900-01-01 13:30:00.000','1900-01-01 14:30:00.000','1900-01-01 17:30:00.000','1900-01-01 18:30:00.000' UNION ALL
SELECT '2017-03-02','000003','上下午','1900-01-01 09:00:00.000','1900-01-01 17:00:00.000','1900-01-01 08:30:00.000','1900-01-01 09:30:00.000','1900-01-01 16:30:00.000','1900-01-01 17:30:00.000' UNION ALL

SELECT '2017-03-03','000015','上午','1900-01-01 08:00:00.000','1900-01-01 12:00:00.000','1900-01-01 07:30:00.000','1900-01-01 08:30:00.000','1900-01-01 11:30:00.000','1900-01-01 12:30:00.000' UNION ALL
SELECT '2017-03-03','000015','下午','1900-01-01 14:00:00.000','1900-01-01 18:00:00.000','1900-01-01 13:30:00.000','1900-01-01 14:30:00.000','1900-01-01 17:30:00.000','1900-01-01 18:30:00.000' UNION ALL
SELECT '2017-03-03','000002','上午','1900-01-01 08:00:00.000','1900-01-01 12:00:00.000','1900-01-01 07:30:00.000','1900-01-01 08:30:00.000','1900-01-01 11:30:00.000','1900-01-01 12:30:00.000' UNION ALL
SELECT '2017-03-03','000002','下午','1900-01-01 14:00:00.000','1900-01-01 18:00:00.000','1900-01-01 13:30:00.000','1900-01-01 14:30:00.000','1900-01-01 17:30:00.000','1900-01-01 18:30:00.000' UNION ALL
SELECT '2017-03-03','000003','上下午','1900-01-01 09:00:00.000','1900-01-01 17:00:00.000','1900-01-01 08:30:00.000','1900-01-01 09:30:00.000','1900-01-01 16:30:00.000','1900-01-01 17:30:00.000'


--考勤原始记录表
IF OBJECT_ID('[checkinout]') IS NOT NULL
    DROP TABLE checkinout
CREATE TABLE checkinout
(
userid VARCHAR(20),  --员工ID
checktime DATETIME   --打卡时间
)
INSERT INTO checkinout
--
SELECT  '000014','2017-03-02 07:51:00.000' UNION ALL
SELECT  '000015','2017-03-02 07:12:00.000' UNION ALL
SELECT  '000015','2017-03-02 07:33:00.000' UNION ALL
SELECT  '000015','2017-03-02 07:54:00.000' UNION ALL
SELECT  '000015','2017-03-02 12:14:00.000' UNION ALL
SELECT  '000015','2017-03-02 12:34:00.000' UNION ALL
SELECT  '000015','2017-03-02 13:50:00.000' UNION ALL
--SELECT  '000015','2017-03-02 18:01:00.000' UNION ALL

SELECT  '000002','2017-03-02 07:54:00.000' UNION ALL
SELECT  '000002','2017-03-02 12:14:00.000' UNION ALL
--SELECT  '000002','2017-03-02 13:50:00.000' UNION ALL
SELECT  '000002','2017-03-02 18:01:00.000' UNION ALL

SELECT  '000003','2017-03-02 08:30:00.000' UNION ALL
SELECT  '000003','2017-03-02 16:59:00.000' UNION ALL
SELECT  '000003','2017-03-02 17:15:00.000' UNION ALL

--SELECT  '000015','2017-03-02 07:34:00.000' UNION ALL
SELECT  '000015','2017-03-03 12:01:00.000' UNION ALL
SELECT  '000015','2017-03-03 13:34:00.000' UNION ALL
SELECT  '000015','2017-03-03 18:01:00.000' UNION ALL

SELECT  '000002','2017-03-03 07:54:00.000' UNION ALL
SELECT  '000002','2017-03-03 12:14:00.000' UNION ALL
SELECT  '000002','2017-03-03 13:50:00.000' UNION ALL
--SELECT  '000002','2017-03-03 18:01:00.000' UNION ALL

SELECT  '000003','2017-03-03 17:15:00.000'

--在有效打卡时间段内有多次打卡记录的,取第一次打卡记录,没有打卡记录或不在有效打卡记录时间段的不记考勤,标记NULL
--求实现如下效果,CHECKTIME1和CHECKTIME2分别是有效打卡时间,双手奉上仅有的10分,求高手帮忙.

/*
dateKQ      USERID   BANCI   StartTime                  EndTime                        CHECKTIME1                 CHECKTIME2
2017-03-02  000015  上午       1900-01-01 08:00:00.000    1900-01-01 12:00:00.000        2017-03-02 07:33:00.000    2017-03-02 12:14:00.000
2017-03-02  000015  下午       1900-01-01 14:00:00.000    1900-01-01 18:00:00.000        2017-03-02 13:50:00.000    null
2017-03-02  000002  上午       1900-01-01 08:00:00.000    1900-01-01 12:00:00.000        2017-03-02 07:54:00.000    2017-03-02 12:14:00.000
2017-03-02  000002  下午       1900-01-01 14:00:00.000    1900-01-01 18:00:00.000        null                       2017-03-02 18:01:00.000
2017-03-02  000003  上下午    1900-01-01 09:00:00.000    1900-01-01 17:00:00.000        2017-03-02 08:30:00.000    2017-03-02 16:59:00.000
2017-03-03  000015  上午       1900-01-01 08:00:00.000    1900-01-01 12:00:00.000        2017-03-02 07:33:00.000    2017-03-02 12:14:00.000
2017-03-03  000015  下午       1900-01-01 14:00:00.000    1900-01-01 18:00:00.000        2017-03-02 13:50:00.000    null
2017-03-03  000002  上午       1900-01-01 08:00:00.000    1900-01-01 12:00:00.000        2017-03-03 07:54:00.000    2017-03-02 12:14:00.000
2017-03-03  000002  下午       1900-01-01 14:00:00.000    1900-01-01 18:00:00.000        2017-03-03 13:50:00.000    null
2017-03-03  000003  上下午    1900-01-01 09:00:00.000    1900-01-01 17:00:00.000        null                       2017-03-03 17:15:00.000
*/

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

8条回答 默认 最新

  • Seeker-Wu 2017-03-11 13:31
    已采纳

    这个是因为4号员工上午也没有打开下午也没有打卡,这个需要和userKQ再次left join 即可,因为在你的userKQ表中没有对4号员工在3月2号的考勤数据,
    所以你的4号员工的统计考勤中不会有3月2号的记录,只会多一条3月3号全天无打卡的记录;
    对应的sql语句如下:
    SELECT
    u1.dateKQ ,
    u1.userid ,
    u1.banci ,
    u1.StartTime ,
    u1.EndTime ,
    tb2.checktime1 ,
    tb2.checktime2
    FROM
    userKQ u1
    LEFT JOIN(
    SELECT
    a.*, b.checkTime2
    FROM
    (
    SELECT
    dateKQ ,
    userid ,
    banci ,
    StartTime ,
    EndTime ,
    min(checktime) AS checktime1
    FROM
    (
    SELECT
    u.*, c.checktime
    FROM
    userKQ u
    JOIN checkinout c ON u.userid = c.userid
    WHERE
    date(c.checktime) = u.dateKQ
    AND(
    time(c.checktime) >= time(u.StartTime1)
    AND time(c.checktime) <= time(u.StartTime2)
    )
    ) tmp1
    GROUP BY
    dateKQ ,
    userid ,
    banci ,
    StartTime ,
    EndTime
    ) a
    LEFT JOIN(
    SELECT
    dateKQ ,
    userid ,
    banci ,
    StartTime ,
    EndTime ,
    min(checktime) AS checktime2
    FROM
    (
    SELECT
    u.*, c.checktime
    FROM
    userKQ u
    JOIN checkinout c ON u.userid = c.userid
    WHERE
    date(c.checktime) = u.dateKQ
    AND(
    time(c.checktime) >= time(u.EndTime1)
    AND time(c.checktime) <= time(u.EndTime2)
    )
    ) tmp2
    GROUP BY
    dateKQ ,
    userid ,
    banci ,
    StartTime ,
    EndTime
    ) b ON(
    a.dateKQ = b.dateKQ
    AND a.userid = b.userid
    AND a.banci = b.banci
    )
    UNION
    SELECT
    b.dateKQ ,
    b.userid ,
    b.banci ,
    b.StartTime ,
    b.EndTime ,
    a.checktime1 ,
    b.checktime2
    FROM
    (
    SELECT
    dateKQ ,
    userid ,
    banci ,
    StartTime ,
    EndTime ,
    min(checktime) AS checktime1
    FROM
    (
    SELECT
    u.*, c.checktime
    FROM
    userKQ u
    JOIN checkinout c ON u.userid = c.userid
    WHERE
    date(c.checktime) = u.dateKQ
    AND(
    time(c.checktime) >= time(u.StartTime1)
    AND time(c.checktime) <= time(u.StartTime2)
    )
    ) tmp1
    GROUP BY
    dateKQ ,
    userid ,
    banci ,
    StartTime ,
    EndTime
    ) a
    RIGHT JOIN(
    SELECT
    dateKQ ,
    userid ,
    banci ,
    StartTime ,
    EndTime ,
    min(checktime) AS checktime2
    FROM
    (
    SELECT
    u.*, c.checktime
    FROM
    userKQ u
    JOIN checkinout c ON u.userid = c.userid
    WHERE
    date(c.checktime) = u.dateKQ
    AND(
    time(c.checktime) >= time(u.EndTime1)
    AND time(c.checktime) <= time(u.EndTime2)
    )
    ) tmp2
    GROUP BY
    dateKQ ,
    userid ,
    banci ,
    StartTime ,
    EndTime
    ) b ON(
    a.dateKQ = b.dateKQ
    AND a.userid = b.userid
    AND a.banci = b.banci
    )
    ) tb2 ON(
    u1.dateKQ = tb2.dateKQ
    AND u1.userid = tb2.userid
    AND u1.banci = tb2.banci
    )
    下面为执行结果图:
    图片说明

    点赞 打赏 评论
  • devmiao 2017-03-11 06:54
    点赞 打赏 评论
  • Seeker-Wu 2017-03-11 08:20

    SELECT
    a.*,b.checkTime2
    FROM
    (
    SELECT
    dateKQ ,
    userid ,
    banci ,
    StartTime ,
    EndTime ,
    min(checktime) AS checktime1
    FROM
    (
    SELECT
    u.*, c.checktime
    FROM
    userKQ u
    JOIN checkinout c ON u.userid = c.userid
    WHERE
    date(c.checktime) = u.dateKQ
    AND(
    time(c.checktime) >=time(u.StartTime1)
    AND time(c.checktime) <=time(u.StartTime2)
    )
    ) tmp1
    GROUP BY
    dateKQ ,
    userid ,
    banci ,
    StartTime ,
    EndTime

    ) a
    left JOIN(
    SELECT
    dateKQ ,
    userid ,
    banci ,
    StartTime ,
    EndTime ,
    min(checktime) AS checktime2
    FROM
    (
    SELECT
    u.*, c.checktime
    FROM
    userKQ u
    JOIN checkinout c ON u.userid = c.userid
    WHERE
    date(c.checktime) = u.dateKQ
    AND(
    time(c.checktime) >=time(u.EndTime1)
    AND time(c.checktime) <=time(u.EndTime2)
    )
    ) tmp2
    GROUP BY
    dateKQ ,
    userid ,
    banci ,
    StartTime ,
    EndTime

    ) b on (a.dateKQ = b.dateKQ and a.userid=b.userid and a.banci=b.banci)

    union

    SELECT
    b.dateKQ,b.userid,b.banci,b.StartTime,b.EndTime,a.checktime1,b.checktime2
    FROM
    (
    SELECT
    dateKQ ,
    userid ,
    banci ,
    StartTime ,
    EndTime ,
    min(checktime) AS checktime1
    FROM
    (
    SELECT
    u.*, c.checktime
    FROM
    userKQ u
    JOIN checkinout c ON u.userid = c.userid
    WHERE
    date(c.checktime) = u.dateKQ
    AND(
    time(c.checktime) >=time(u.StartTime1)
    AND time(c.checktime) <=time(u.StartTime2)
    )
    ) tmp1
    GROUP BY
    dateKQ ,
    userid ,
    banci ,
    StartTime ,
    EndTime

    ) a
    right JOIN(
    SELECT
    dateKQ ,
    userid ,
    banci ,
    StartTime ,
    EndTime ,
    min(checktime) AS checktime2
    FROM
    (
    SELECT
    u.*, c.checktime
    FROM
    userKQ u
    JOIN checkinout c ON u.userid = c.userid
    WHERE
    date(c.checktime) = u.dateKQ
    AND(
    time(c.checktime) >=time(u.EndTime1)
    AND time(c.checktime) <=time(u.EndTime2)
    )
    ) tmp2
    GROUP BY
    dateKQ ,
    userid ,
    banci ,
    StartTime ,
    EndTime

    ) b on (a.dateKQ = b.dateKQ and a.userid=b.userid and a.banci=b.banci)

    上面为满足你需求的查询sql,因为mysql不支持full join 所以采用left join 和 right join 以及union来完成的;
    步骤如下;
    首先查询班次分类的上班打卡的第一个有效时间对应的sql为:
    SELECT
    dateKQ ,
    userid ,
    banci ,
    StartTime ,
    EndTime ,
    min(checktime) AS checktime1
    FROM
    (
    SELECT
    u.*, c.checktime
    FROM
    userKQ u
    JOIN checkinout c ON u.userid = c.userid
    WHERE
    date(c.checktime) = u.dateKQ
    AND(
    time(c.checktime) >=time(u.StartTime1)
    AND time(c.checktime) <=time(u.StartTime2)
    )
    ) tmp1
    GROUP BY
    dateKQ ,
    userid ,
    banci ,
    StartTime ,
    EndTime

    然后查询班次分类的下班打卡的第一个有效时间对应的sql为:
    SELECT
        dateKQ ,
        userid ,
        banci ,
        StartTime ,
        EndTime ,
        min(checktime) AS checktime2
    FROM
        (
            SELECT
                u.*, c.checktime
            FROM
                userKQ u
            JOIN checkinout c ON u.userid = c.userid
            WHERE
                date(c.checktime) = u.dateKQ
            AND(
                time(c.checktime) >=time(u.EndTime1)
                AND time(c.checktime) <=time(u.EndTime2)
            )
        ) tmp2
    GROUP BY
        dateKQ ,
        userid ,
        banci ,
        StartTime ,
        EndTime
    
        再将上述查出的上班有效时间和下班有效时间做表连接,因为采用mysql数据库不支持full join,所以最后合并比较麻烦一点,最终的sql为:
        SELECT
    a.*,b.checkTime2
    

    FROM
    (
    SELECT
    dateKQ ,
    userid ,
    banci ,
    StartTime ,
    EndTime ,
    min(checktime) AS checktime1
    FROM
    (
    SELECT
    u.*, c.checktime
    FROM
    userKQ u
    JOIN checkinout c ON u.userid = c.userid
    WHERE
    date(c.checktime) = u.dateKQ
    AND(
    time(c.checktime) >=time(u.StartTime1)
    AND time(c.checktime) <=time(u.StartTime2)
    )
    ) tmp1
    GROUP BY
    dateKQ ,
    userid ,
    banci ,
    StartTime ,
    EndTime

    ) a
    left JOIN(
    SELECT
    dateKQ ,
    userid ,
    banci ,
    StartTime ,
    EndTime ,
    min(checktime) AS checktime2
    FROM
    (
    SELECT
    u.*, c.checktime
    FROM
    userKQ u
    JOIN checkinout c ON u.userid = c.userid
    WHERE
    date(c.checktime) = u.dateKQ
    AND(
    time(c.checktime) >=time(u.EndTime1)
    AND time(c.checktime) <=time(u.EndTime2)
    )
    ) tmp2
    GROUP BY
    dateKQ ,
    userid ,
    banci ,
    StartTime ,
    EndTime

    ) b on (a.dateKQ = b.dateKQ and a.userid=b.userid and a.banci=b.banci)

    union

    SELECT
    b.dateKQ,b.userid,b.banci,b.StartTime,b.EndTime,a.checktime1,b.checktime2
    FROM
    (
    SELECT
    dateKQ ,
    userid ,
    banci ,
    StartTime ,
    EndTime ,
    min(checktime) AS checktime1
    FROM
    (
    SELECT
    u.*, c.checktime
    FROM
    userKQ u
    JOIN checkinout c ON u.userid = c.userid
    WHERE
    date(c.checktime) = u.dateKQ
    AND(
    time(c.checktime) >=time(u.StartTime1)
    AND time(c.checktime) <=time(u.StartTime2)
    )
    ) tmp1
    GROUP BY
    dateKQ ,
    userid ,
    banci ,
    StartTime ,
    EndTime

    ) a
    right JOIN(
    SELECT
    dateKQ ,
    userid ,
    banci ,
    StartTime ,
    EndTime ,
    min(checktime) AS checktime2
    FROM
    (
    SELECT
    u.*, c.checktime
    FROM
    userKQ u
    JOIN checkinout c ON u.userid = c.userid
    WHERE
    date(c.checktime) = u.dateKQ
    AND(
    time(c.checktime) >=time(u.EndTime1)
    AND time(c.checktime) <=time(u.EndTime2)
    )
    ) tmp2
    GROUP BY
    dateKQ ,
    userid ,
    banci ,
    StartTime ,
    EndTime

    ) b on (a.dateKQ = b.dateKQ and a.userid=b.userid and a.banci=b.banci)

    根据所给出的图片说明数据查询结果如下图,望采纳,谢谢

    点赞 打赏 评论
  • nxybcce 2017-03-11 11:41

    还有点问题,就是在班次表中还有这样一行数据:

     INSERT INTO userKQ
    SELECT '2017-03-03','000004','上下午','1900-01-01 09:00:00.000','1900-01-01 17:00:00.000','1900-01-01 08:30:00.000','1900-01-01 09:30:00.000','1900-01-01 16:30:00.000','1900-01-01 17:30:00.000'
    

    但在考勤考勤原始记录表中没有有效的刷卡记录,应该在统计结果中有这样两行

     2017-03-02  000003  上下午    1900-01-01 09:00:00.000    1900-01-01 17:00:00.000        null                       null
     2017-03-03  000003  上下午    1900-01-01 09:00:00.000    1900-01-01 17:00:00.000        null                       null
    

    但没有,但如何修改呢,多谢.

    点赞 打赏 评论
  • nxybcce 2017-03-11 11:50

    噢, USERID写错了,应该是这两行

     2017-03-02  000004  上下午    1900-01-01 09:00:00.000    1900-01-01 17:00:00.000        null                       null
     2017-03-03  000004  上下午    1900-01-01 09:00:00.000    1900-01-01 17:00:00.000        null                       null
    
    点赞 打赏 评论
  • nxybcce 2017-03-11 12:58

    测试了下,加这段好像可以做到(SQL2008)

     SELECT u.dateKQ,u.userid,u.banci,u.StartTime,u.EndTime,m.checktime1,m.checktime2
      FROM userKQ u  FULL JOIN m ON m.dateKQ=u.dateKQ AND m.userid=u.userid AND u.StartTime=m.StartTime AND u.EndTime=m.Endtime 
    
    /*
    2017-03-02  000015  上午  1900-01-01 08:00:00.000 1900-01-01 12:00:00.000 2017-03-02 07:33:00.000 2017-03-02 12:14:00.000
    2017-03-02  000015  下午  1900-01-01 14:00:00.000 1900-01-01 18:00:00.000 2017-03-02 13:50:00.000 NULL
    2017-03-02  000002  上午  1900-01-01 08:00:00.000 1900-01-01 12:00:00.000 2017-03-02 07:54:00.000 2017-03-02 12:14:00.000
    2017-03-02  000002  下午  1900-01-01 14:00:00.000 1900-01-01 18:00:00.000 NULL    2017-03-02 18:01:00.000
    2017-03-02  000003  上下午   1900-01-01 09:00:00.000 1900-01-01 17:00:00.000 2017-03-02 08:30:00.000 2017-03-02 16:59:00.000
    2017-03-02  000004  上下午   1900-01-01 09:00:00.000 1900-01-01 17:00:00.000 NULL    NULL
    2017-03-03  000015  上午  1900-01-01 08:00:00.000 1900-01-01 12:00:00.000 NULL    2017-03-03 12:01:00.000
    2017-03-03  000015  下午  1900-01-01 14:00:00.000 1900-01-01 18:00:00.000 2017-03-03 13:34:00.000 2017-03-03 18:01:00.000
    2017-03-03  000002  上午  1900-01-01 08:00:00.000 1900-01-01 12:00:00.000 2017-03-03 07:54:00.000 2017-03-03 12:14:00.000
    2017-03-03  000002  下午  1900-01-01 14:00:00.000 1900-01-01 18:00:00.000 2017-03-03 13:50:00.000 NULL
    2017-03-03  000003  上下午   1900-01-01 09:00:00.000 1900-01-01 17:00:00.000 NULL    2017-03-03 17:15:00.000
    2017-03-03  000004  上下午   1900-01-01 09:00:00.000 1900-01-01 17:00:00.000 NULL    NULL
    */
    

    多加了些数据测试,整体执行速度不算很快,但得到了想要的结果,不知道有没有更好的方法.

    点赞 打赏 评论
  • nxybcce 2017-03-11 13:12

    计算出1000行结果,用时22秒

    点赞 打赏 评论
  • Seeker-Wu 2017-03-11 13:34

    你所给出的表格应该是已你的考勤表为统计依据的,而你checkinout中则是员工日常打卡的记录;
    也就是说你userKQ表中出现了某个员工,几号的考勤记录,则他在checkinout中应该有对应的打卡记录,才算其正常上下班;
    如果你考勤表中都没有某天(如周末)该员工的记录,则其统计结果应该就没有其对应的数据,因为不需要统计。
    所以你说的应该有两条记录,从数据库表的设计角度和逻辑角度来说,可能性很小;也许你想实现的是那样的效果,但是你所给出的表结构只能是在userKQ中有的
    就有,没有的就不会出现。以上分析,仅供参考,如果答案还算满意,请采纳,谢谢。

    点赞 打赏 评论

相关推荐 更多相似问题