我使用的是糟糕的 CMS 系统。

它的事件模块可以按明确的日期/时间存储事件，例如2011-08-04 13:30:00 或每周重复事件，其中重复日期存储为基于 0 的整数，时间添加到日期/时间字段(其中数据为 0)，例如0000-00-00 13:30:00。

我需要了解接下来 2 周内即将举行的所有事件。这意味着我需要所有 每周重复发生的事件以及接下来 2 周内的明确日期。

我不是 SQL 专家。我写了这个查询...

SELECT `id`,
       `name`,
       `info`,
       `date_time`,
       `weekly_day`
FROM   `events` AS a
WHERE  `active` = true
       AND `id` IN (SELECT `id`
                    FROM   `events`
                    WHERE  WEEK(`date_time`) BETWEEN WEEK(NOW()) AND WEEK(
                                                     DATE_ADD(NOW(), INTERVAL
                                                     1 WEEK)))
        OR DATE(`date_time`) = 0
ORDER  BY Time_to_sec(IF(TIME(`date_time`), TIME(`date_time`),
                                            Concat(EXTRACT(HOUR FROM `date_time`
                                                   ), ":",
                                            EXTRACT(MINUTE FROM `date_time`)))),
          IF(DATE(`date_time`), Dayofweek(`date_time`), `weekly_day` + 1) ASC 

(请注意，上面的 NOW() 已替换为一些 PHP，以 MySQL 格式回显当前日期。MySQL 服务器的时区与我想要的时区不同。)

这成功获得了我想要的所有记录，但没有正确排序。例如，6 月 9 日的事件出现在 6 月 2 日的事件之前。

如何解决我的查询？

干杯。

更新

我已经想到了这个，但我仍然卡住了......尽管日期看起来是正确的，但它不会显示每周事件。

SELECT `id`,
       `name`,
       `info`,
       `date_time`,
       `weekly_day`,
       ( DATE(`date_time`) = 0 ) AS `weekly`
FROM   `events`
WHERE  `active` = true
       AND IF (DATE(`date_time`), WEEK(`date_time`),
           DATE_ADD(
               Concat(DATE(NOW()), " ", Concat(EXTRACT(HOUR FROM `date_time`),
                                        ":",
                                        EXTRACT
                                        (
                                        MINUTE FROM `date_time`))), INTERVAL
           `weekly_day`
                                                                    DAY))
           BETWEEN WEEK(
           NOW()) AND WEEK(DATE_ADD(NOW(), INTERVAL 2 WEEK))
ORDER  BY `date_time` ASC 

我无法使用 TIME() 来提取时间部分，因为它一直返回 NULL。

我做错了什么？

最佳答案

我会分两步来做:

1. 将接下来两周的重复条目转换为两个 DATETIME 值。这将是一个子查询。
2. 收集同期的非经常性分录。这将是第二个子查询。

两个子查询的 UNION 为您提供所有事件及其实际发生的日期和时间；正确地排序是微不足道的。

如果您需要记录信息是否来自定期 session ，请在两个子查询的选择列表中保留一些内容(可能是简单的“R”或“N”)，以便您可以判断该信息是否来自重复 session 原始条目是经常性的或非经常性的。

我不是 MySQL DATETIME 操作方面的专家 - 但我确实了解另一个 DBMS(即 Informix)的领域。出于我在本次讨论中的目的，表中只有两个有趣的列:date_time 和 weekly_day。我假设您正在使用 1 = 星期日，7 = 星期六的 MySQL 约定。由于我们需要 2 周的重复事件信息，因此生成 3 周并过滤掉不相关的信息可能是最简单的。

给定引用日期和一周中的整数天，我们可以使用以下方法从中获取三个值:

 refdate - DAYOFWEEK(refdate) + day_of_week
 refdate - DAYOFWEEK(refdate) + day_of_week + INTERVAL  7 DAYS
 refdate - DAYOFWEEK(refdate) + day_of_week + INTERVAL 14 DAYS

这些产生三个日期；第一个可能在过去，或者第三个可能在未来太远了(因此需要丢弃)。当日期是自引用日期以来的天数时(​​如在 Informix 中)，这是算术。要将第三行翻译成 MySQL，假设 'refdate' 是 NOW()，我们似乎必须写(非常冗长 - 我认为 Informix 对于 DATETIME 操作非常冗长):

DATE_ADD(DATE_ADD(DATE_SUB(NOW(), DAYOFWEEK(NOW())), INTERVAL weekly_day DAY),
    INTERVAL 14 DAY)

在这里，我假设我可以将 weekly_day 中的整数转换为天数；必要时进行调整。这些公式为每个经常性条目提供了三个 DATE 值，其中两个是相关的。为了增加时间，我认为我们需要:

DATE_ADD(DATE_ADD(DATE_ADD(DATE_SUB(NOW(), DAYOFWEEK(NOW())), INTERVAL weekly_day DAY),
    INTERVAL 14 DAY), INTERVAL TIME(date_time) HOUR_SECOND)

所以，我最初的回答中提到的第一个子查询本身需要是一个 3-way UNION。

SELECT 'R' AS info_mode,
       DATE_ADD(DATE_ADD(DATE_ADD(DATE_SUB(NOW(), DAYOFWEEK(NOW())),
                                  INTERVAL weekly_day DAY),
                         INTERVAL 14 DAY),
                INTERVAL TIME(date_time) HOUR_SECOND) AS event_time
  FROM events
UNION
SELECT 'R' AS info_mode,
       DATE_ADD(DATE_ADD(DATE_ADD(DATE_SUB(NOW(), DAYOFWEEK(NOW())),
                                  INTERVAL weekly_day DAY),
                         INTERVAL 7 DAY),
                INTERVAL TIME(date_time) HOUR_SECOND) AS event_time
  FROM events
UNION
SELECT 'R' AS info_mode,
       DATE_ADD(DATE_ADD(DATE_SUB(NOW(), DAYOFWEEK(NOW())),
                                  INTERVAL weekly_day DAY),
                INTERVAL TIME(date_time) HOUR_SECOND) AS event_time
  FROM events;

您现在需要过滤“接下来的两周”。那是哪个日期/时间范围？从引用时刻 - 指定 NOW()?还是从基准日开始？还是从基准日的次日开始？一切都可能是合理的；由于代码似乎使用了引用模式(而且它是最简单的)，我们将采用:

SELECT *
  FROM (SELECT 'R' AS info_mode,
               DATE_ADD(DATE_ADD(DATE_ADD(DATE_SUB(NOW(), DAYOFWEEK(NOW())),
                                          INTERVAL weekly_day DAY),
                                 INTERVAL 14 DAY),
                        INTERVAL TIME(date_time) HOUR_SECOND) AS event_time
          FROM events
        UNION
        SELECT 'R' AS info_mode,
               DATE_ADD(DATE_ADD(DATE_ADD(DATE_SUB(NOW(), DAYOFWEEK(NOW())),
                                          INTERVAL weekly_day DAY),
                                 INTERVAL 7 DAY),
                        INTERVAL TIME(date_time) HOUR_SECOND) AS event_time
          FROM events
        UNION
        SELECT 'R' AS info_mode,
               DATE_ADD(DATE_ADD(DATE_SUB(NOW(), DAYOFWEEK(NOW())),
                                          INTERVAL weekly_day DAY),
                        INTERVAL TIME(date_time) HOUR_SECOND) AS event_time
          FROM events) AS r
  WHERE r.event_time BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 14 DAY);

这给出了重复发生的事件。 r.event_time 上的过滤条件可以在 UNION 的每个分支中下推(复制)，但我暂时将其保留在原处。

使用以下方法找到非重复事件:

SELECT 'N' AS info_mode,
       date_time AS event_time
  FROM events AS e
 WHERE e.date_time BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 14 DAY)
   AND DATE(e.date_time) != DATE '0000-00-00';

因此，我们可以创建这两个查询的联合。可以将 event_time 上的条件从两个子查询中拖出(我们可以希望优化器然后“将其放回原处”)。 非重复事件子查询简单地成为 UNION 的第四部分，导致:

SELECT *
  FROM (SELECT 'R' AS info_mode,
               DATE_ADD(DATE_ADD(DATE_ADD(DATE_SUB(NOW(), DAYOFWEEK(NOW())),
                                          INTERVAL weekly_day DAY),
                                 INTERVAL 14 DAY),
                        INTERVAL TIME(date_time) HOUR_SECOND) AS event_time
          FROM events
        UNION
        SELECT 'R' AS info_mode,
               DATE_ADD(DATE_ADD(DATE_ADD(DATE_SUB(NOW(), DAYOFWEEK(NOW())),
                                          INTERVAL weekly_day DAY),
                                 INTERVAL 7 DAY),
                        INTERVAL TIME(date_time) HOUR_SECOND) AS event_time
          FROM events
        UNION
        SELECT 'R' AS info_mode,
               DATE_ADD(DATE_ADD(DATE_SUB(NOW(), DAYOFWEEK(NOW())),
                                          INTERVAL weekly_day DAY),
                        INTERVAL TIME(date_time) HOUR_SECOND) AS event_time
          FROM events
        UNION
        SELECT 'N' AS info_mode,
               date_time AS event_time
          FROM events
         WHERE DATE(e.date_time) != DATE '0000-00-00'
       ) AS e
 WHERE e.event_time BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 14 DAY);

因此，这应该会为您提供事件类型列表(重复、非重复)以及 future 14 天内发生的事件。您只需添加回 id、name 和 info 列(此外，为了调试，原始的 date_time和 weekly_day 列)到 UNION 中的 4 个查询中的每一个，以及 e.event_time 上的 ORDER BY 子句(以及您要添加的任何打破平局的列)。

请善待任何语法或特定于 DBMS 的语义错误；我希望概念清晰(并且正确)，即使由于不熟悉 MySQL 的细节而导致细节不正确。

关于mysql - 我如何使用这个复杂的表格结构按日期和时间订购我的商品？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/6195013/