mysql - 使用条件计算mySQL中的时间差

Question

我有这个查询，必须选择1行，其中状态是“OKQ”，“PI”，“CBK”，“NRP”，“NEW”，如果“OKQ”，则 (status_time + 86400) 必须SMALLER 小于当前时间戳 NOW，以便在执行时查询的状态必须具有 status_time + 86400 秒(已经是过去)，依此类推，如查询中所述:

SELECT id FROM leads t JOIN (
    SELECT "OKQ" as status, 86400 as diff UNION ALL
    SELECT "PI" as status, 3600 as diff UNION ALL
    SELECT "CBK" as status, 60 as diff UNION ALL
    SELECT "NRP" as status, 3600 as diff UNION ALL
    SELECT "NEW" as status, 1 as diff
) s USING (status)
WHERE t.status_time > date_sub(now(), interval s.diff second)
ORDER BY RAND()
LIMIT 1

现在，此查询返回 0 作为 num_rows，尽管它的大多数记录为“NEW”，并且“NEW”的 status_time 为4 天前，因此 NOW(当前时间戳)远大于 4 天前 + 1 秒，因此必须选择一行。

Answer 1

您的查询应如下所示，您应该使用 < 而不是 >

从潜在客户中选择 id t JOIN ( 选择“OKQ”作为状态，86400 作为 diff UNION ALL 选择“PI”作为状态，3600 作为 diff UNION ALL 选择“CBK”作为状态，60 作为 diff UNION ALL 选择“NRP”作为状态，3600 作为 diff UNION ALL 选择“NEW”作为状态，1 作为差异) s 使用(状态)WHERE t.status_time < date_sub(now(), 间隔 s.diff 秒)按兰德排序()限制 1