MySQL 在几天内查询排类 - 更简单的方法？

Question

我有一些关于 MySQL 的报告，下面的代码是我目前对这个问题的解决方案。

在我开始考虑员工轮类时间超过午夜之前，这似乎很简单，因此必须调整查询。现在我让 php 根据日期/时间跨度生成不同的查询，如下所示:

如果工作类次在同一天(即:2 天内上午 8 点至晚上 8 点):

SELECT <select statements>
FROM <from statements>
WHERE
(
    (Date = '2012-04-16' AND Time BETWEEN '08:00:00' AND '20:00:00')
    OR
    (Date = '2012-04-02' AND Time BETWEEN '08:00:00' AND '20:00:00')
);

如果类次超过午夜，情况会变得复杂(即:2 天内晚上 8 点到早上 8 点):

SELECT <select statements>
FROM <from statements>
WHERE
(
    (
        (Date = '2012-04-16' AND Time >= '20:00:00')
        OR
        (Date = '2012-04-17' AND Time <= '08:00:00')
    )
    OR
    (
        (Date = '2012-04-17' AND Time >= '20:00:00')
        OR
        (Date = '2012-04-18' AND Time <= '08:00:00')
    )
);

正如您想象的那样，随着我每天添加到报告中，这些查询变得非常冗长和繁重。必须有一种更聪明的方法来做到这一点 - 谁能提供任何见解？

Answer 1

如果你想拥有更优雅的代码，这部分:

    (Date = '2012-04-16' AND Time >= '20:00:00')
    OR
    (Date = '2012-04-17' AND Time <= '08:00:00')

可以改成:

    (Date, Time) >= (DATE('2012-04-16'), TIME('20:00:00'))
    AND 
    (Date, Time) <= (DATE('2012-04-17'), TIME('08:00:00'))

(Date, Time) 上的复合索引对您的版本和上述版本都有帮助。

---

如果您有几个这样的条件，例如您的示例:

SELECT <select statements>
FROM <from statements>
WHERE
(
    (
        (Date = '2012-04-16' AND Time >= '20:00:00')
        OR
        (Date = '2012-04-17' AND Time <= '08:00:00')
    )
    OR
    .....
    OR 
    (
        (Date = '2012-05-27' AND Time >= '20:00:00')
        OR
        (Date = '2012-05-28' AND Time <= '08:00:00')
    )
)

你可以把它变成:

SELECT <select statements>
FROM <from statements>
  CROSS JOIN
      ( SELECT TIME('20:00:00') AS start_time
             , TIME('08:00:00') AS end_time
      ) AS cc
  JOIN
      ( SELECT d                   AS this_day
             , d + INTERVAL 1 DAY  AS next_day
        FROM
          ( SELECT DATE('2012-04-16') AS d
          UNION ALL
            ...
          UNION ALL
            SELECT '2012-05-27'
          ) AS s 
      ) AS selected
     ON  (Date, Time) >= (selected.this_day, cc.start_time)
     AND (Date, Time) <= (selected.next_day, cc.end_time  )