MySQL选择当前时间之后的记录

Question

这是一个食品服务系统...我有一张名为detalle_regimen_paciente的表，它存储了一份饮食计划，包括星期一到星期日的早餐（1代表星期一，7代表星期日，按照智利时间），并且还有具体的用餐时间。

+---+---------+-----------+--------------+---+
|id | name    |hora_carga |hora_servicio |day|
+---+---------+-----------+--------------+---+
|1  |breakfast|08:00      |09:00         |1  |
+---+---------+-----------+--------------+---+
|2  |lunch    |10:00      |13:00         |1  |
+---+---------+-----------+--------------+---+
|3  |breakfast|08:00      |09:00         |2  |
+---+---------+-----------+--------------+---+
|2  |lunch    |10:00      |13:00         |2  |
+---+---------+-----------+--------------+---+

我已经为这个查询苦恼了两天（数据库中有更多的内容……一些n:m关系是从这个消费表中获取一个员工的总消费），我发现问题在于我找不到一种方法来仅选择星期一08:00的餐点（记录ID＃1）使用当前日期和当前时间......

因此，在星期一的07:58，它应该只返回ID为1的记录，因为它是下一个要服务的餐点，也是下一个要向员工收费的餐点。

+---+---------+-------------+---+
|1  |breakfast|08:00        |1  |
+---+---------+-------------+---+

这是目前的SQL查询语句，但它似乎只过滤了日期...

select detalle_regimen_paciente.hora_carga
from
detalle_regimen_paciente
where
detalle_regimen_paciente.hora_servicio > CURTIME() AND
detalle_regimen_paciente.hora_carga > CURTIME()
AND
detalle_regimen_paciente.dia = (select DAYOFWEEK(CURDATE())-1)

也许是我提出的问题不对或者误用了时间函数。请在这里帮忙解决一下，非常感谢您的帮助。

- Marcos Vargas Moran

---

你能定义一下 hora_carga 是什么吗？ - golja

Answer 1

SELECT 
    *                          --- select the columns you need (hora_carga ?)
FROM
  ( SELECT *  
    FROM detalle_regimen_paciente   
    WHERE  hora_servicio > CURTIME()     
         AND
           dia = DAYOFWEEK(CURDATE())-1
       OR 
         dia > DAYOFWEEK(CURDATE())-1
    ORDER BY dia, hora_servicio
      LIMIT 1
  ) AS a
UNION ALL
SELECT *
FROM
  ( SELECT *     
    FROM detalle_regimen_paciente   
    ORDER BY dia, hora_servicio
      LIMIT 1
  ) AS b
ORDER BY dia DESC, hora_servicio DESC
  LIMIT 1
; 

- ypercubeᵀᴹ

---

为 dia 添加一种模 7 比较方式，这将是一个很好的答案... - MvG

@MvG：是的，这个查询会从周日晚餐一直持续到周日午夜。 - ypercubeᵀᴹ

你可以很好地消除一层嵌套，因为在FROM部分之后的所有子查询都是不必要的。 - MvG

@MvG：我在子查询中使用了LIMIT，所以嵌套是必要的。 - ypercubeᵀᴹ

根据文档的说明，使用括号就足以将LIMIT限制在UNION中的一个SELECT部分。 - MvG

@MvG：哦，是的，你说得对，这是特定于MySQL语法的。 - ypercubeᵀᴹ

Answer 2

如果您的查询限制结果为：

• 当前日期以及
• 仍处于未来时间的时间

那么您可以使用以下方法获取最近的时间：

SELECT … FROM … WHERE …
ORDER BY hora_servicio ASC
LIMIT 1

这个查询将结果按小时排序，并选择第一个结果。在一天的最后一餐之后，您需要等到午夜才能获得非空结果。您可以使用 union 来解决这个问题：

(SELECT 0 AS tomorrow, … FROM …
 WHERE dia = ((DAYOFWEEK(CURDATE()) + 5) MOD 7) + 1
   AND hora_servicio > CURTIME()
)
UNION ALL
(SELECT 1 AS tomorrow, … FROM …
 WHERE dia = DAYOFWEEK(CURDATE()) -- this really is the next day
)
ORDER BY tomorrow, hora_servicio
LIMIT 1

你的代码可能也需要这个工作日计算。以下是它的工作原理:

                                        Su Mo Tu We Th Fr Sa
  DAYOFWEEK(CURDATE())                   1  2  3  4  5  6  7
 (DAYOFWEEK(CURDATE()) + 5)              6  7  8  9 10 11 12
((DAYOFWEEK(CURDATE()) + 5) MOD 7)       6  0  1  2  3  4  5
((DAYOFWEEK(CURDATE()) + 5) MOD 7) + 1   7  1  2  3  4  5  6

您的版本在星期日将计算为0而不是7。使用MySQL的DAYOFWEEK来引用下一天可能有点令人困惑，因此值得进行注释。

作为dia = ((DAYOFWEEK(CURDATE()) + 5) MOD 7) + 1的替代方案，您可以编写dia MOD 7 = DAYOFWEEK(CURDATE()) - 1，因为这将两边都使用基于零的数字。但是，结果可能不太有效率，因为数据库必须计算左侧的值，这会破坏索引和类似的优化。因此，我更愿意将所有计算放到右侧。

感谢@ypercube让我考虑到下一天的问题。

如果要考虑未来不止一天（我希望您不必为太多人做到这一点），则可以像这样处理：

SELECT dia, hora_servicio
FROM detalle_regimen_paciente
WHERE dia <> ((DAYOFWEEK(CURDATE()) + 5) MOD 7) + 1
   OR hora_servicio > CURTIME()
ORDER BY (dia - DAYOFWEEK(CURDATE()) + 1) MOD 7 ASC,
         hora_servicio ASC
LIMIT 1

这里的思路是，第一个 ORDER BY 项计算未来的天数：该值对于当前日期为0，明天为1，后天为2，以此类推。对于当天的餐点，我们只选择当前时间之后的餐点。对于其他所有日期，任何时间都可以。

- MvG