在一定条件下计算mySQL中的时差

Question

我有一个必须选择1行的查询，其中status是"OKQ“、"PI”、"CBK“、"NRP”、"NEW"，如果"OKQ“(status_time + 86400)必须小于当前时间戳NOW，那么在执行查询时，状态必须有status_time + 86400秒，这已经存在于过去的e 210中，等等：

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连接(选择"OKQ“作为状态，86400作为diff选择"PI”作为状态，3600作为diff选择"CBK“作为状态，60作为diff全部选择"NRP”作为状态，3600作为diff全部选择"NEW“作为状态，1作为diff )s使用( t.status_time