消除错误
这就是我正在努力解决的问题。
这是一个时钟系统，我想消除错误。
尝试使用 select 语句来获取同一用户有重复“上班打卡”或重复“打卡下班”的所有记录。
您有一个 employee_ID 和一个方向。
数据看起来像这样：
ID.......employee_ID.........Direction
1. .........1..................In
2. .........2..................In
3. .........3..................In
4. .........1..................Out
5. .........2..................Out
6. .........3..................In
因此记录 6 将是一个错误。
Here is what I am struggling with.
This is a clocking system and I want to take out the errors.
Trying a select statment to get all the records where there is a duplicate "clock in" or duplicate "clock out" for the same user.
You have an employee_ID and a direction.
The data looks something like this:
ID.......employee_ID.........Direction
1. .........1..................In
2. .........2..................In
3. .........3..................In
4. .........1..................Out
5. .........2..................Out
6. .........3..................In
So record 6 would be an error.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
正如你所说，它是一个时钟系统，你需要有一个时间戳或一个自动增量 id 列，基于此你可以在插入之前检查每个 emp Id 的最后一个事件（最新时间戳或最大 id）（作为触发器的一部分） 。
as you say its a clocking system, you need to have a timestamp or a auto increment id column, based on which you can check the last event (latest timestamp or the max id) for every emp Id before inserting (as part of trigger).
您还可以考虑每个班次有一排。该行将保存上班打卡和下班打卡时间戳。通过这种设计，您的 datediff 操作会简单得多。
You could also think about having one row for each shift. This row would hold both the clock-in and clock-out timestamp. Your datediff operations would be much less complex with this design.