防止创建重叠的日期范围

问题描述：

我在日历中有一个特定事件，该事件分配给具有sdate(ShowDate)和hdate(HideDate)的storeID.现在，对于具有此storeID的每个新事件，我需要确保与现有日期时间段没有重叠.那有意义吗?我怎么做?我读了一些东西，但不知道如何仅在不重叠的情况下插入".

I have a certain event in the calendar assigned to a storeID that has a sdate (ShowDate) and an hdate (HideDate). Now for every new event with this storeID, I need to make sure that there's no overlap with an existing date period. Does that make sense? How do I do that? I read about stuff but couldn't figure out how to "insert only if not overlap".

答

通常，如果您有两个范围以S1..E1和S2..E2作为范围的开始和结束值，则您会有一个重叠如果:

In general, if you have two ranges with S1..E1 and S2..E2 as the start and end values of the ranges, then you have an overlap if:

S1< E2和
S2< E1

这是对称的，这很好(并且令人放心).您将需要仔细决定那些小于"运算是否应该小于或等于"；两者都有意义，这取决于您存储数据的方式(开放式，封闭式，半开式，开闭式和封闭式开仓范围等).您可以看到图表关于问题确定两个日期范围是否重叠" .

This is symmetric, which is good (and reassuring). You will need to decide carefully whether those 'less than' operations should be 'less than or equal'; both can make sense, depending on how you store your data (open vs closed vs half-open or open-closed and closed-open ranges, etc). You can see a diagram of the possibilities on the question 'Determine whether two date ranges overlap'.

在您的上下文中，"ShowDate"对应于开始日期，而"HideDate"对应于结束日期.

In your context, the 'ShowDate' corresponds to the start date, and the 'HideDate' corresponds to the end date.

听起来好像您想执行条件插入" ，也.在这种情况下，假设您的StoreID为1001，ShowDate为2012-03-21，HideDate为2012-03-28，则您可能会这样写:

It sounds rather as if you want to do a 'conditional insert', too. In this case, assuming that your StoreID is 1001, the ShowDate is 2012-03-21 and the HideDate is 2012-03-28, then you might write:

INSERT INTO Calendar(StoreID, ShowDate, HideDate, ...)
   SELECT 1001, '2012-03-21', '2012-03-28', ...
     FROM Dual
    WHERE NOT EXISTS(SELECT * FROM Calendar C2
                      WHERE C2.StoreID   = 1001
                        AND C2.ShowDate  < '2012-03-28'
                        AND '2012-03-21' < C2.HideDate
                    );

选择"列表中的值是要添加到日历"表中的值.子选择意味着您将为您的新值的数据获得0行(因为有重叠)或1行(因为没有重叠).

The values in the SELECT-list are those you want to add to the Calendar table. The sub-select means you get either 0 rows (because there is an overlap) or 1 row (because there is no overlap) with the data for your new value.

防止创建重叠的日期范围

相关推荐