Question

有sql高手嘛,有个高深的需求求指教!想拿到每个用户每天最早和最晚两次打卡记录

有sql高手嘛,有个高深的需求求指教!

一张打卡表叫A, 有id,userid,time三个字段,
想拿到每个用户每天最早和最晚两次打卡记录,其他的都不要,求这个sql该怎么写啊!

Answer 1

假设打卡时间字段是c_time，因为time本身是Oracle系统关键字，不能直接用作字段名称。实现您想要的功能SQL如下：

select userid, min(c_time) as "最早时间", max(c_time) as "最晚时间"

from A

group by userid, to_char(c_time, 'YYYY-MM-DD')

order by userid, to_char(c_time, 'YYYY-MM-DD')

假设表A数据如下图：

执行上述SQL后的结果如下如：

也就是说对于当天只有一次打卡记录的人员，其最早打卡时间=最晚打卡时间。

特别说明：排序字段的先后顺序可以根据您的需要调整，如果想集中看某人的可以先按人排序；如果集中想看某日期的则可以先按时间排序。

追问

请问我这个不是从0点开始为一个自然日的,是从早上4点为一个自然日,这样该如何做呢?已增加悬赏,谢谢

追答

补充问一下：您的意思是说每日凌晨4点至次日凌晨4点为一个自然日，是这个意思吗？

追问

是的!! 要求每人每天4点开始到第二天4点结束的最早最晚时间

追答

好的，针对您提出的问题，只需要把语句调整如下即可：

select userid,

to_char(c_time - 4 / 24, 'YYYY-MM-DD') as "打卡日期",

min(c_time) as "最早时间",

max(c_time) as "最晚时间"

from A

group by userid, to_char(c_time - 4 / 24, 'YYYY-MM-DD')

order by userid, to_char(c_time - 4 / 24, 'YYYY-MM-DD')

投入测试数据如下，增加红框部分003人员凌晨4点前后的打卡记录：

按照您的需求描述预期结果为：该员工

2月28号    上下班打卡时间相同，均为2018-03-01 03:39:31

3月1号    上班打卡时间为：2018-03-01 04:39:31

下班打卡时间为：2018-03-02 03:59:59

3月2号    上下班打卡时间相同，均为2018-03-02 04:00

验证结果如下图：

与预期结果完全一致。

特别说明：因为您错后4小时的特殊要求，自然天如期已经无法标记打卡记录所属日期，例如1号凌晨4点之前的打卡记录实际应该属于上月末（2月28号/2月29号），因此我增加了“打卡日期”一列，以标记考勤所属的日期。

追问

再次感谢!不过我这里是mysql数据库,并无to_char函数,在网上查询说可以替代成DATE_FORMAT函数,可是按照要求替换后并无作用,还请帮忙,谢谢!

我的mysql中的sql如下:

追答

因为我这里没有mysql的环境，无法复现您的执行结果，您能给我看一下执行结果吗？是有报错还是说可以执行但是无效果。
提醒一下：to_char(c_time - 4 / 24, 'YYYY-MM-DD') 这个计算的含义是在实际时间的基础上减去4小时，因为Oracle对日期的计算是以“天”为单位的，因此减去4小时就是减去4/24天。请您自行测试确认一下MySQL在日期计算的时候c_time+1这种操作是在什么单位上进行的，是增加1天还是1小时还是1分钟等。这个可能是您解决问题的关键。

追问

你好,大神,是不是只要对这个时间进行加减到4h就可以了?

如上图是我在网上查找的修改每日分割点为4点的语句,

确实可以根据4点分割,可是有一个新问题,就是他只能统计这个用户在这张表里所有数据的最大值和最小值,不再按照天去分组了,如果把 红框内内容去掉,也就是恢复成0点为默认分割,

数据就可以按照 每人每天来划分,请您帮忙看下,谢谢!!!

追答

把这个时间分割的公式复制到group by语句当中去。因为group by 标记的是分组的依据，也就是说你的语句还是按照自然日分组的，当然就是默认0点分割了呀。

追问

你好!我按照您所说的进行了修改,确实成功了!效果如图!

分割日期与时间在第一行,

但是好像还是有看到效果,有些问题

追答

你的时间计算公式写错了，你应该把4改成-4

追问

改成-4就变成20点了...

追答

这个时候你就不用看具体的时间了，因为你已经相当于人为定义了每日的分割时间，此时对于你来说日期是关注的对象，时间就不是你关注的对象了。你有两种修改方式：
1、把select中的add_date函数和dateformat函数换一个位置，也就是说先计算时间再把时间格式化，保证显示的时间只有日期
2、在现有的基础上外面再套一层dateformat函数，将时间格式化为yyyy-mm-dd的形式，不看具体时间。