mysql - 重复事件，SQL 查询

Question

我知道关于重复事件的问题很常见，但除了与日历应用程序有关的重复事件之外，我还没有找到可以回答这个关于重复事件的特定问题的答案。主要区别在于我们应用程序中的事件。将只会出现在报告中或单独出现，而不是出现在日历格式中，尽管它们在许多方面非常相似，也许只是日历相关的包袱更少。

以类似于日历应用程序的方式。事件可以一次性发生，也可以重复发生每个星期四或每个月的第一个星期一，直到将来某个预先设定的时间。

事件存储在一个事件表中，该表包含开始日期和结束日期以及“循环类型 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?

或

https://www.rfc-editor.org/rfc/rfc5545

我已经检查过它们，它们非常有用，但与我们预期的不同。

TIA

Answer 1

除非我遗漏了什么，否则答案非常简单。我没有意识到 UNION 可以通过使用别名在公共(public)列上排序，即使这些列来自不同的表。所以完整的查询是:

SELECT DISTINCT(e.eventid),n.nameid,n.firstname,n.lastname,d.dt <strong>AS dait</strong>,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 <strong>AS dait</strong>,'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 <strong>dait</strong>;

有人指出，使用表格查找日期是有风险的，因为日期最终会用完，这是事实，但是计算日期是否是，例如，一个月中的第一个星期一(或第二个星期一) ，或者第四个或者第四个也是最后一个)，似乎是比我现在想要进入的更复杂的 SQL 代码。