php - Mysql 根据上次运行选择记录

Question

我有一个包含以下字段的表

project_name       VARCHAR (100)
running_frequency  enum ('daily', 'weekly', 'monthly')
last_job_run       DATETIME

我想根据 last_job_run 字段选择现在到期的记录。例如，如果任务昨天运行并选择了每天，那么我需要一个查询来选择今天、明天等的记录。

是否可以在不涉及 PHP 的情况下完成此操作？

Answer 1

用途:

SELECT t.*
  FROM TABLE t
 WHERE (     t.running_frequency = 'daily' 
         AND t.last_job_run < CURRENT_DATE)
    OR (     t.running_frequency = 'weekly' 
         AND t.last_job_run <= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY))
    OR (     t.running_frequency = 'monthly' 
         AND t.last_job_run <= DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH))

作为 UNION 语句，这可能运行得更快:

SELECT x.*
  FROM TABLE x
 WHERE x.running_frequency = 'daily' 
   AND x.last_job_run < CURRENT_DATE
UNION ALL
SELECT y.*
  FROM TABLE y
 WHERE y.running_frequency = 'weekly' 
   AND y.last_job_run <= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)
UNION ALL
SELECT z.*
  FROM TABLE z
 WHERE z.running_frequency = 'monthly' 
   AND z.last_job_run <= DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH)