SQL面试:防止日期范围重叠

本文介绍了SQL面试:防止日期范围重叠的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

说有一个约会表，用于列出具有startDatetime和endDatetime的经理(或HR)列表，然后如何仔细设计该表，以使其不接受同一经理重复的下一个条目(如果他/她拥有)与其他人约会.

Say there is an appointment_booking table for a list of Managers (or HRs) with startDatetime and endDatetime, then how do one design the table carefully such that it doesn't accept next entry that overlaps for same manager if he/she has appointment with some other person.

如果经理:A从 2016-01-01 11:00到2016-01-01 14:00 与 Employee-1 进行了约会那么如果 Employee-2 (或其他雇员)尝试从 20-16-01-01 13:00到16:00 进行约会，则不允许

If Manager: A has a appointment from 2016-01-01 11:00 to 2016-01-01 14:00 with Employee-1 then if Employee-2 (or someother employee) tries to book an appointment from 20-16-01-01 13:00 to 16:00 then it shouldn't allow.

注意:这是关于设计表的，因此不鼓励使用触发器/过程.

Note: It is about designing the table, so triggers/procedures isn't encouraged.

推荐答案

您可以插入时间片，而不是插入范围.您可以将切片切成想要的宽度，但假装您可以一次预订30分钟的经理.要从11:30到12:00进行预订，您需要插入一行时间值为11:30的行.要从11:30到12:30预订，您需要插入两行，其中一列在11:30，另一列在12:00.然后，您可以使用主键约束或唯一约束来防止预订过多.

Instead of inserting ranges, you could insert slices of time. You could make the slices as wide as you want, but pretend you can book a manager for 30 minutes at a time. To book from 11:30 to 12:00, you'd insert a row with the time value at 11:30. To book from 11:30 to 12:30, you'd insert two rows, one at 11:30, the other at 12:00. Then you can just use a primary key constraint or unique constraint to prevent over booking.

create table appointment_booking ( manager char not null, startSlice DateTime, visiting_employee varchar2(255), primary key (manager, startSlice) )

我知道这与开始和结束时间完全不适合您的表格前提，但是如果您可以控制表格的结构，那将是可行的.

I know this doesn't exactly fit your premise of the table with a start and end time, but if you have control over the table structure, this would work.