求一考勤统计实现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
*/

这个是因为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
)
下面为执行结果图：

http://bbs.csdn.net/topics/390637968?page=1

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

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

union

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

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

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

 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

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

噢, 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

测试了下,加这段好像可以做到(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
*/

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

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

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