我正在开发一个简单的PHP/MySQL应用程序。作为其中的一部分,我希望能够对重复事件进行建模,但我需要能够查询两个日期之间发生的所有事件(包括重复事件)。活动只有一个日期,一天中的时间并不重要。
我一直在研究这一点,并研究了各种方法,包括日历重复/重复事件-最佳存储方法和重复日历事件以及一些期末数学。
然而,我在网上找到的任何支持这一点的数据库模式示例似乎都只支持查询某一天发生的事件。不支持在一系列日期之间发生的事件。
作为一个抽象的例子
事件表(具有某种重复表示):
Event | Start Date | Repeats
-------------------------------------
Meeting | 10/Dec/2012 | Every 7 days
Lunch | 10/Dec/2012 | Every 1 days
目标抽象查询SELECT Events BETWEEN 09/Dec/2012 AND 20/Dec/2012
的结果
Event | Date | Repeats
-------------------------------------
Meeting | 10/Dec/2012 | Every 7 days
Meeting | 17/Dec/2012 | Every 7 days
Lunch | 10/Dec/2012 | Every 1 days
Lunch | 11/Dec/2012 | Every 1 days
Lunch | 12/Dec/2012 | Every 1 days
Lunch | 13/Dec/2012 | Every 1 days
etc...
Lunch | 20/Dec/2012 | Every 1 days
是否有一个数据库模式可以支持这类查询?对于两天内发生的任何事件(包括重复事件),我将如何在该模式上进行查询?
或者可能是一种用于重复事件的设计模式?
我会创建一个只有一个名为id
的列的计数表,并用0到500的数字填充该表。现在我们可以很容易地使用它来进行选择,而不是使用while循环。
Id
-------------------------------------
0
1
2
etc...
然后我将事件存储在一个具有Name as varchar
、startdate as datetime
和repeats as int
的表中
Name | StartDate | Repeats
-------------------------------------
Meeting | 2012-12-10 00:00:00 | 7
Lunch | 2012-12-10 00:00:00 | 1
现在我们可以使用计数表来选择两个日期之间的所有日期,方法是:
SELECT DATE_ADD('2012-12-09 00:00:00',INTERVAL Id DAY) as showdate
FROM `tally`
WHERE (DATE_ADD('2012-12-09 00:00:00',INTERVAL Id DAY)<='2012-12-20 00:00:00')
ORDER BY Id ASC
ShowDate
-------------------------------------
2012-12-09 00:00:00
2012-12-10 00:00:00
2012-12-11 00:00:00
2012-12-12 00:00:00
2012-12-13 00:00:00
2012-12-14 00:00:00
2012-12-15 00:00:00
2012-12-16 00:00:00
2012-12-17 00:00:00
2012-12-18 00:00:00
2012-12-19 00:00:00
2012-12-20 00:00:00
然后,我们将其加入事件表中,以计算开始日期和显示日期之间的差异。我们用repeats
列来划分这个结果,如果余数是0
,我们就有匹配。
所有组合成为:
SELECT E.Id, E.Name, E.StartDate, E.Repeats, A.ShowDate, DATEDIFF(E.StartDate, A.ShowDate) AS diff
FROM events AS E, (
SELECT DATE_ADD('2012-12-09 00:00:00',INTERVAL Id DAY) as showdate
FROM `tally`
WHERE (DATE_ADD('2012-12-09 00:00:00',INTERVAL Id DAY)<='2012-12-20 00:00:00')
ORDER BY Id ASC
) a
WHERE MOD(DATEDIFF(E.StartDate, A.ShowDate), E.Repeats)=0
AND A.ShowDate>=E.StartDate
导致
Id | Name |StartDate | Repeats | ShowDate | diff
---------------------------------------------------------------------------------
1 | Meeting | 2012-12-10 00:00:00 | 7 | 2012-12-10 00:00:00 | 0
2 | Lunch | 2012-12-10 00:00:00 | 1 | 2012-12-10 00:00:00 | 0
2 | Lunch | 2012-12-10 00:00:00 | 1 | 2012-12-11 00:00:00 | -1
2 | Lunch | 2012-12-10 00:00:00 | 1 | 2012-12-12 00:00:00 | -2
2 | Lunch | 2012-12-10 00:00:00 | 1 | 2012-12-13 00:00:00 | -3
2 | Lunch | 2012-12-10 00:00:00 | 1 | 2012-12-14 00:00:00 | -4
2 | Lunch | 2012-12-10 00:00:00 | 1 | 2012-12-15 00:00:00 | -5
2 | Lunch | 2012-12-10 00:00:00 | 1 | 2012-12-16 00:00:00 | -6
1 | Meeting | 2012-12-10 00:00:00 | 7 | 2012-12-17 00:00:00 | -7
2 | Lunch | 2012-12-10 00:00:00 | 1 | 2012-12-17 00:00:00 | -7
2 | Lunch | 2012-12-10 00:00:00 | 1 | 2012-12-18 00:00:00 | -8
2 | Lunch | 2012-12-10 00:00:00 | 1 | 2012-12-19 00:00:00 | -9
2 | Lunch | 2012-12-10 00:00:00 | 1 | 2012-12-20 00:00:00 | -10
现在你可以(也应该!)加快速度。例如,直接将日期存储在表中,这样您就可以直接选择所有日期,而不是使用带有dateadd的计数表。每一件你可以缓存并且不需要重新计算的东西都是好的。
我不太理解你的目标。。。也许您在SQL中寻找UNIQUE函数?