删除时间重叠的行
我正在尝试删除一些与时间相关的冲突日期，这是一个开始时间和停止时间的表，我想编写一个查询来删除重叠的数据，在这种情况下我想删除 13： 00 行和 13:30 行（第 6/7 行），因为这些时间已包含在 12:00 到 14:00< /代码> 行（行3/4）。 VALUE_ENUM 为 1 的所有行都是开始时间，VALUE_ENUM 为 0 的所有行都是停止时间。
以下是我的表中数据的示例：
SITE_ID ------- DEV_ID ------- SCHEDULE_TIME ------- VALUE_ENUM
---------------------------------------------------------------
1 3000 09:30:00 1
1 3000 11:30:00 0
1 3000 12:00:00 1
1 3000 14:00:00 0
1 3000 13:00:00 1
1 3000 13:30:00 0
1 3000 16:30:00 1
1 3000 18:30:00 0
I'm trying to delete some conflicting date related to the times, this is a table of start times and stop times, and I want to write a query to delete overlapping data, in this case I would want to delete 13:00 row and 13:30 row(rows 6/7) because those times are already covered with the 12:00 to 14:00 row(rows 3/4). All the rows where VALUE_ENUM are 1 are start times and all the rows where VALUE_ENUM are 0 are stop times.
Here is an example of what the data in my table looks like:
SITE_ID ------- DEV_ID ------- SCHEDULE_TIME ------- VALUE_ENUM
---------------------------------------------------------------
1 3000 09:30:00 1
1 3000 11:30:00 0
1 3000 12:00:00 1
1 3000 14:00:00 0
1 3000 13:00:00 1
1 3000 13:30:00 0
1 3000 16:30:00 1
1 3000 18:30:00 0
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您似乎想要在
schedule_time小于上一行时删除该行。但是如何对行进行排序呢？还有其他专栏吗？否则，一旦删除，下一个插入很可能会在该位置，并且下次您将最终错误地删除很多行。不确定如何在单个 sql 语句中完成此操作，但过程算法很简单 - 将 last_schedule_time 初始化为 null，然后对每一行进行比较；如果小于则删除或将 last_schedule_time 分配给 current。
It seems you want to delete a row when its
schedule_timeis less than the previous row. But how do your order the rows? Is there another column? Otherwise, once you delete the next insert will mostly likely be in that position and the next time you will end up deleting lot of rows wrongly.Not sure how this can be done in a single sql statement but the procedural algorithm is straightforward -- initialize last_schedule_time to null and then on every row compare; delete if less or assign last_schedule_time to current.
感谢您的帮助，但我能够通过将特定系统的先前时间设置为临时变量来解决答案，然后与下一个时间进行比较，看看它们是否落在临时时间之间，如果落在临时时间之间，我就跳过了完全插入，再次感谢您的意见和建议。
Thanks for the help, but I was able to solve the answer by setting previous times for specific systems to temp variables and then did a comparison with the next times to see if they fell between the temp times, and if they did I just skipped the insert entirely, once again Thank you for the input and suggestions.