周期性事件，SQL查询 [英] Recurring Events, SQL Query
问题描述
我知道有关重复事件的问题很常见，但是除了日历应用程序之外，我找不到能够回答有关重复事件这一特定问题的答案.主要区别在于我们应用程序中的事件.仅在报告中或以自己的方式显示，而不是以日历格式显示，尽管它们在很多方面都非常相似，也许与日历相关的行李更少.
以类似于日历应用程序的方式.事件可以一次性发生也可以重复发生.每个月的每个星期四或第一个星期一，直到将来的某个预设时间.
事件存储在事件表中，该表包含开始和结束日期以及重复类型ID".如果重复类型"为无"，则开始日期和结束日期将相同.事件表将ID保留到一个单独的表中，该表包含事件类型名称，例如会议"或每周报告"
还有另一个表，其中包含重复类型"列表，例如无重复"，每个星期一"，每月的第一个星期一"和每月的最后一个星期六".
为便于查找，另一张表包含1960年至2060年的日期列表以及有关每个日期的相关信息，例如是否为星期一，以及该月出现在哪个星期一.
这样可以进行如下查找:
这正是查找重复发生的事件所需要的，其输出如下:
要获取没有重复发生的事件，可以使用更简单的查询:
我的问题是:如何结合这些查询以得出一个包含所有事件的列表，这些事件按日期顺序包括重复发生的事件和非重复发生的事件?
这些是表和从中缩短的选择，为简便起见，已删除了一些列和所有索引:).出于相同原因，未包含名称"表.
我们并非绝对希望使用上面的代码或表布局，欢迎任何可行的解决方案.请不要将我指向:
或
http://tools.ietf.org/html/rfc5545
我已经检查了它们，它们非常有用，但没有达到我们的预期.
TIA
除非我缺少某些内容，否则答案非常简单.我还没有意识到UNION可以通过使用别名在公用列上进行排序，即使这些列来自不同的表也是如此.因此，完整的查询将是:
有人指出，使用表格查找日期是有风险的，因为日期最终会用完，这是对的，但是要计算日期是否是例如一个月中的第一个星期一(或第二个星期一) ，或第四或最后一个)，似乎比我现在想要的要复杂得多.
I am aware that questions about recurring events are common but I have not been able to find one with an answer to this specific question about recurring events other than those to do with calendar apps. The main difference being that the events in our app. will only ever be seen either in reports or by themselves rather than in a calendar format although in many ways they are very similar, maybe just with less of the baggage associated with calendars.
In a similar way to a calendar app. events can either occur on a one-off basis or can be recurring E.g. every Thursday or first Monday of every month, until some pre-set time in the future.
The events are stored in an event table which contains the start and end dates and the 'recurrency type id'. If the 'recurrency type' is 'None' then the start and end dates will be the same. The event table holds an id to a separate table which holds the event type name, E.g. 'Meeting' or 'Weekly report'
There is a further table which contains the list of 'recurrency types' E.g. 'No recurrence', 'Every Monday', 'First Monday of month' and 'Last Saturday of month'.
To make lookups easier another table contains a list of dates from 1960 to 2060 along with relevant information about each date, like whether it is a Monday, and which occurrence of Monday it is in the month.
This allows a lookup like:
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;
Which is exactly what is required for finding recurring events, giving output like:
+-----------+---------------+-------------------+-----------+------------+-------------------------------+ | 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 |
To get none recurring events a simpler query can be used:
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;
This gives output very similar to the first query but, crucially, the dates are from the events table rather than the dates table.
My question is: How can I combine these queries to come up with one list that contains all the events, both recurring and non-recurring in date order?
These are the tables and shortened selections from them, some columns and all indexes have been removed for brevity :). The 'names' table has not been included for the same reason.
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 | +------------+----------+------+-------+-----+-----+------------+--------+
We are not absolutely set on using the above code or table layout, any working solutions would be welcome. Please do not point me towards:
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?
or
http://tools.ietf.org/html/rfc5545
I have checked them out and they were very useful but not doing the same as we intend.
TIA
Unless I'm missing something the answer is suprisingly simple. I had not realised that UNIONs can be sorted on common columns by using an alias, even if those columns are from different tables. So the full query would be:
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;
It's been pointed out that using a table for looking up dates is a risk because the dates will eventually run out, which is true, but calculating whether a date is, for example, the first Monday in a month (or the second, or fourth or maybe fourth and last), seems like a more complex bit of SQL code than I want to get into at the moment.
这篇关于周期性事件，SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
