重复事件，SQL查询
重复事件，SQL查询
我知道关于循环事件的问题很常见，但除了与日历应用有关的问题之外，我还没有找到一个关于循环事件的问题的答案，主要区别在于我们的应用中的事件只能在报告中或单独地看到，而不是以日历格式显示，尽管从很多方面来看它们非常相似，也许只是与日历相关的负担较少。\n与日历应用程序类似，事件可以是一次性的，也可以是重复的，例如每个星期四或每个月的第一个星期一，直到未来某个预设时间为止。\n事件存储在一个事件表中，该表包含开始日期、结束日期和“重复类型ID”。如果“重复类型”为“无”，则开始日期和结束日期将相同。事件表中保存了指向另一个表的ID，该表保存了事件类型名称，例如“会议”或“每周报告”。\n还有一个包含“重复类型”列表的表，例如“无重复”、“每个星期一”、“每月第一个星期一”和“每月最后一个星期六”。\n为了使查找更容易，另一个表包含从1960年到2060年的日期列表，以及有关每个日期的相关信息，例如它是否是星期一，以及它在该月中的第几个星期一。\n这允许进行如下查询：\n
SELECT DISTINCT(e.eventid),n.nameid,n.firstname,n.lastname,d.dt,r.recurring FROM dates d LEFT JOIN recurringtypes r /* 如果事件每周重复，例如“每个星期一” */ ON (r.rectypeid BETWEEN 2 AND 8 AND r.day = d.dow) /* 如果事件每月重复，例如“每月第一个星期一” */ OR ((r.rectypeid BETWEEN 9 AND 36) AND r.day = d.dow AND r.occurrence = d.occurrence) /* 如果事件每月最后一周重复，例如“每月最后一个星期一” */ 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;\n这正是查找重复事件所需的，输出结果如下：\n
\n+-----------+---------------+-------------------+-----------+------------+-------------------------------+\n| eventid | nameid | lastname | firstname | dt | recurring |\n+-----------+---------------+-------------------+-----------+------------+-------------------------------+\n| 3291788 | 1728449 | smith | zoe | 2012-02-02 | First Thursday, every month |\n| 3291797 | 1765432 | | | 2012-02-05 | First Sunday, every month |\n| 3291798 | 1730147 | | | 2012-02-05 | First Sunday, every month |\n| 3291803 | 1790061 | Carpenter | Richie | 2012-02-06 | Every Monday |\n| 3291805 | 1790061 | Carpenter | Richie | 2012-02-08 | Second Wednesday, every month |\n| 3291803 | 1790061 | Carpenter | Richie | 2012-02-13 | Every Monday |\n| 3291799 | 1790061 | Carpenter | Richie | 2012-02-15 | Third Wednesday, every month |\n| 3291803 | 1790061 | Carpenter | Richie | 2012-02-20 | Every Monday |\n\n为了获取非重复事件，可以使用更简单的查询：\n
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;\n这将输出与第一个查询非常相似的结果，但关键是日期来自事件表而不是日期表。\n我的问题是：如何将这些查询组合在一起，以得到按日期顺序包含所有事件的列表？\n
\n这些是表和它们的简化选择，为简洁起见删除了一些列和所有索引。由于同样的原因，未包含“names”表。\n
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 | +------------+----------+------+-------+-----+-----+------------+--------+\n
\n我们对使用上述代码或表格布局并不完全确定，任何有效的解决方案都将受到欢迎。请不要指向以下内容：\nHow would you store possibly recurring times?\nWhat\'s the best way to model recurring events in a calendar application?\nShould I store dates or recurrence rules in my database when building a calendar app?\n或者\nhttps://www.rfc-editor.org/rfc/rfc5545\n我已经查看了它们，它们非常有用，但与我们的意图不同。\n谢谢。
问题的出现原因是查询结果中重复出现了一些行，即使events.rectypeid = 1（没有重复事件）。可能是因为事件和重复类型表不同步导致的。可能缺少一个'AND'在'OR'之后，但无法确定应该是什么。
解决方法是将条件e.rectypeid <= 1改为e.rectypeid <= 1 AND e.eventid IS NOT NULL。另一种尝试是将e.rectypeid < 1或将e.eventid IS NOT NULL添加到WHERE子句中。
然而，这些选项都没有起作用。考虑尝试不同的方法，例如将两个查询的结果通过UNION合并在一起，尽管我（有限的）经验告诉我，UNION只是将两个完全独立的查询连接在一起，没有什么实际用途。
可能的解决方法是在WHERE子句中添加e.rectypeid > 1，或在LEFT JOIN events e on e.rectypeid = r.rectypeid OR (e.rectypeid=r.rectypeid AND e.rectypeid < 1)中添加条件。然而，这些方法都没有成功，可能是因为重复类型表没有与rectype <= 1的情况进行关联，因此任何对非重复事件的其他引用都注定会失败。
建议尝试在与重复类型表进行连接的地方添加条件OR r.rectypedid <= 1，或者使用UNION来解决问题。
感谢您的建议。我在下面发布了答案（使用了我之前未意识到的一种使用UNION的方法）。
问题：Recurring Events, SQL Query的出现原因和解决方法
在上述内容中，作者提出了一个关于Recurring Events（重复事件）和SQL查询的问题。作者希望通过SQL查询来获取在指定日期范围内重复发生的事件，并按照日期对查询结果进行排序。作者已经提供了一个用于解决问题的SQL查询语句，并附带了一些解释。
原因：
- 作者之前没有意识到可以使用别名来排序UNION操作的结果集，即使这些列来自不同的表。
- 作者希望能够使用一个简单的SQL查询来获取在指定日期范围内重复发生的事件，并按照日期进行排序。
解决方法：
- 使用UNION操作将两个查询的结果集合并在一起。
- 在第一个查询中，使用LEFT JOIN操作将dates表与recurringtypes表和events表进行连接，并根据重复事件的类型和日期来筛选结果。
- 在第二个查询中，使用LEFT JOIN操作将events表与names表进行连接，并根据事件的起始日期来筛选结果。
- 使用DISTINCT关键字去除重复的结果。
- 使用ORDER BY关键字按照日期进行排序。
以下是解决问题的SQL查询语句：
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代码更复杂。