这是一个食品服务系统……
 我有一个名为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星期一用餐(这是记录号#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)

也许我只是错误或错过时间功能.

请帮帮忙.

thx提前.

解决方法:

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
;