我知道，有关重复事件的问题很常见，但是除了日历应用程序之外，我无法找到一个有关重复事件这一特定问题的答案。主要区别在于应用程序中的事件。尽管从很多方面来看，它们非常相似，也许与日历相关联的行李较少，所以只能在报告中看到，也不能以日历形式看到它们，而只能以日历格式看到。
以类似于日历应用程序的方式。事件可以一次性发生也可以重复发生。每个月的每个星期四或第一个星期一，直到将来的某个预设时间为止。
事件存储在事件表中，该表包含开始日期和结束日期以及“重复类型ID”。如果“重复类型”为“无”，则开始日期和结束日期将相同。事件表将ID保留到一个单独的表中，该表包含事件类型名称，例如“ session ”或“每周报告”
还有一个表格，其中包含“重复类型”列表，例如“无重复”，“每个星期一”，“每月的第一个星期一”和“每月的最后一个星期六”。
为了使查找更容易，另一个表包含一个1960年至2060年的日期列表以及有关每个日期的相关信息，例如是否为星期一，以及该月中出现星期一的时间。
这样可以进行如下查找:
SELECT DISTINCT(e.eventid),n.nameid,n.firstname,n.lastname,d.dt,r.recurring
FROM dates d
LEFT JOIN recurringtypes r
/* if event recurring every week E.g. 'Every Monday' */
ON (r.rectypeid BETWEEN 2 AND 8 AND r.day = d.dow)
/* if event recurring every month E.g. 'First Monday, every month' */
OR ((r.rectypeid BETWEEN 9 AND 36) AND r.day = d.dow AND r.occurrence = d.occurrence)
/* if event recurring every last week of month E.g. 'Last Monday, every month' */
OR (r.rectypeid >= 37 AND r.day = d.dow and r.islast = d.islast)
LEFT JOIN events e on e.rectypeid = r.rectypeid
LEFT JOIN eventtypes t ON e.eventtypeid = t.eventtypeid
LEFT JOIN names n ON e.namesid = n.namesid
WHERE (d.dt BETWEEN '2012/02/01' AND '2012/05/01')
ORDER BY d.dt;
+-----------+---------------+-------------------+-----------+------------+-------------------------------+
| eventid | nameid | lastname | firstname | dt | recurring |
+-----------+---------------+-------------------+-----------+------------+-------------------------------+
| 3291788 | 1728449 | smith | zoe | 2012-02-02 | First Thursday, every month |
| 3291797 | 1765432 | | | 2012-02-05 | First Sunday, every month |
| 3291798 | 1730147 | | | 2012-02-05 | First Sunday, every month |
| 3291803 | 1790061 | Carpenter | Richie | 2012-02-06 | Every Monday |
| 3291805 | 1790061 | Carpenter | Richie | 2012-02-08 | Second Wednesday, every month |
| 3291803 | 1790061 | Carpenter | Richie | 2012-02-13 | Every Monday |
| 3291799 | 1790061 | Carpenter | Richie | 2012-02-15 | Third Wednesday, every month |
| 3291803 | 1790061 | Carpenter | Richie | 2012-02-20 | Every Monday |
SELECT n.nameid,n.lastname,n.firstname,e.firstdate,e.eventid,'No' as Recurring
FROM events e
LEFT JOIN names n ON n.names = e.namesid
AND e.rectypeid <= 1
AND e.firstdate BETWEEN '2012/02/01' AND '2012/05/01'
AND e.eventid IS NOT NULL ORDER BY e.firstdate;
我的问题是:如何组合这些查询以得出一个包含所有事件的列表，这些事件按日期顺序重复发生和不重复发生？
这些是表格和从中的简短选择，为简洁起见，一些列和所有索引均已删除。出于相同原因，未包含“名称”表。
CREATE TABLE events (
eventid int(11) NOT NULL AUTO_INCREMENT,
eventtypeid int(11) DEFAULT '0',
firstdate date DEFAULT '1960-01-01' COMMENT 'First event',
lastdate date DEFAULT '1960-01-01' COMMENT 'Last event',
rectypeid int(11) DEFAULT '1'
);
+---------+-------------+------------+------------+-----------+
| eventid | eventtypeid | firstdate | lastdate | rectypeid |
+---------+-------------+------------+------------+-----------+
| 3291803 | 16 | 2012-02-03 | 2012-04-11 | 3 |
| 3291797 | 8 | 2012-02-12 | 2012-02-22 | 9 |
| 3291798 | 5 | 2012-02-12 | 2012-02-12 | 9 |
| 3291788 | 8 | 2012-05-24 | 2015-01-16 | 13 |
| 3291805 | 10 | 2012-01-04 | 2012-02-14 | 19 |
| 3291799 | 16 | 2012-02-07 | 2012-10-24 | 26 |
| 3291804 | 5 | 2012-02-03 | 2012-08-22 | 41 |
+---------+-------------+------------+------------+-----------+
CREATE TABLE cmseventtypes (
eventtypeid int(11) NOT NULL AUTO_INCREMENT,
eventtype varchar(50) DEFAULT '' COMMENT 'Event type AKA name'
);
+-------------+----------------------+
| eventtypeid | eventype |
+-------------+----------------------+
| 1 | Follow up meeting |
| 2 | Reminder email due |
| 3 | Monthly meeting |
| 4 | Weekly report |
| 5 | Golf practice |
+------------------------------------+
CREATE TABLE recurringtypes (
rectypeid int(11) NOT NULL AUTO_INCREMENT,
recurring varchar(40) DEFAULT '',
day tinyint(4) DEFAULT '0',
occurrence tinyint(4) DEFAULT '0',
islast tinyint(4) DEFAULT '0'
);
+-----------+---------------------------+------+------------+--------+
| rectypeid | recurring | day | occurrence | islast |
+-----------+---------------------------+------+------------+--------+
| 1 | No | 0 | 0 | 0 |
| 2 | Every Sunday | 1 | 0 | 0 |
| 3 | Every Monday | 2 | 0 | 0 |
| 4 | Every Tuesday | 3 | 0 | 0 |
| 5 | Every Wednesday | 4 | 0 | 0 |
| 6 | Every Thursday | 5 | 0 | 0 |
| 7 | Every Friday | 6 | 0 | 0 |
| 8 | Every Saturday | 7 | 0 | 0 |
| 9 | First Sunday, every month | 1 | 1 | 0 |
| 10 | First Monday, every month | 2 | 1 | 0 |
+-----------+---------------------------+------+------------+--------+
CREATE TABLE dates (
dt date NOT NULL COMMENT 'Date',
daycount mediumint(9) NOT NULL DEFAULT '1',
year smallint(6) NOT NULL DEFAULT '1970',
month tinyint(4) NOT NULL DEFAULT '1',
dom tinyint(4) NOT NULL DEFAULT '1',
dow tinyint(4) NOT NULL DEFAULT '1',
occurrence tinyint(4) NOT NULL DEFAULT '0',
islast tinyint(1) NOT NULL DEFAULT '0'
);
+------------+----------+------+-------+-----+-----+------------+--------+
| dt | daycount | year | month | dom | dow | occurrence | islast |
+------------+----------+------+-------+-----+-----+------------+--------+
| 2012-02-02 | 734900 | 2012 | 2 | 2 | 5 | 1 | 0 |
| 2012-02-03 | 734901 | 2012 | 2 | 3 | 6 | 1 | 0 |
| 2012-02-04 | 734902 | 2012 | 2 | 4 | 7 | 1 | 0 |
| 2012-02-05 | 734903 | 2012 | 2 | 5 | 1 | 1 | 0 |
| 2012-02-06 | 734904 | 2012 | 2 | 6 | 2 | 1 | 0 |
| 2012-02-07 | 734905 | 2012 | 2 | 7 | 3 | 1 | 0 |
| 2012-02-08 | 734906 | 2012 | 2 | 8 | 4 | 2 | 0 |
| 2012-02-09 | 734907 | 2012 | 2 | 9 | 5 | 2 | 0 |
+------------+----------+------+-------+-----+-----+------------+--------+
我们并非绝对希望使用上面的代码或表布局，欢迎任何可行的解决方案。请不要将我指向:
How would you store possibly recurring times?
What's the best way to model recurring events in a calendar application?
Should I store dates or recurrence rules in my database when building a calendar app?
或者
http://tools.ietf.org/html/rfc5545
我已经检查了它们，它们非常有用，但未达到我们的预期目的。
TIA
最佳答案
除非我缺少任何东西，否则答案非常简单。我没有意识到UNION可以通过使用别名在公用列上进行排序，即使这些列来自不同的表也是如此。因此，完整的查询将是:
SELECT DISTINCT(e.eventid),n.nameid,n.firstname,n.lastname,d.dt AS dait,r.recurring
FROM dates d
LEFT JOIN recurringtypes r
/* if event recurring every week E.g. 'Every Monday' */
ON (r.rectypeid BETWEEN 2 AND 8 AND r.day = d.dow)
/* if event recurring every month E.g. 'First Monday, every month' */
OR ((r.rectypeid BETWEEN 9 AND 36) AND r.day = d.dow AND r.occurrence = d.occurrence)
/* if event recurring every last week of month E.g. 'Last Monday, every month' */
OR (r.rectypeid >= 37 AND r.day = d.dow and r.islast = d.islast)
LEFT JOIN events e on e.rectypeid = r.rectypeid
LEFT JOIN eventtypes t ON e.eventtypeid = t.eventtypeid
LEFT JOIN names n ON e.namesid = n.namesid
WHERE (d.dt BETWEEN '2012/02/01' AND '2012/05/01')
UNION
SELECT e.eventid,n.nameid,n.lastname,n.firstname,e.firstdate AS dait,'No' as Recurring
FROM events e
LEFT JOIN names n ON n.names = e.namesid
AND e.rectypeid <= 1
WHERE e.firstdate BETWEEN '2012/02/01' AND '2012/05/01'
ORDER BY dait;
有人指出，使用表格查找日期是有风险的，因为日期最终会用完，这是对的，但是要计算日期是否是例如一个月中的第一个星期一(或第二个或第四个)或也许是倒数第四)，似乎比我现在想讲的要复杂得多的SQL代码。
关于mysql - 周期性事件，SQL查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/9277834/