mySQL条件选择？

Question

表：id、status、time

$now = time(); // current timestamp

$statuses = array (
    'ST1' => 60,  // (seconds)
    'ST2' => 120,  // (seconds)
    'ST3' => 180,  // (seconds)
);

$query = 'SELECT id FROM table WHERE status IN("ST1", "ST2", "ST3") AND time ... LIMIT 1';

现在，我想从表中选择1 row的id：

如果status为ST1：(time + 60) > $now

如果status为ST2：(时间+ 120) > $now

如果status为ST3：(时间+ 180) > $now

因此，如果该行具有列status = "ST1“，它将检查列time + 60是否大于当前时间戳$now，依此类推。

Answer 1

只需使用基本逻辑：

SELECT l.id
FROM leads l 
WHERE ((l.status = 'ST1' and l.time > date_sub(now(), interval 60 second)) or
       (l.status = 'ST2' and l.time > date_sub(now(), interval 120 second)) or
       (l.status = 'ST3' and l.time > date_sub(now(), interval 180 second)) 
      )
LIMIT 1;

我假设(time + xx) > $now指的是秒。此外，我鼓励您使用数据库时间，而不是传递它。(当然，如果您有充分的理由从应用程序传递时间，则可以用$now替换now() --或者更好的做法是使用参数。)

老实说，我可能会把它放在派生表中：

SELECT l.id
FROM leads l JOIN
     (SELECT 'ST1' as status, 60 as diff UNION ALL
      SELECT 'ST2' as status, 120 as diff UNION ALL
      SELECT 'ST3' as status, 180 as diff
     ) s
     ON l.status = s.status
WHERE l.time > date_sub(now(), interval s.diff second)
LIMIT 1;

Answer 2

使用CASE语句：

SELECT id FROM table WHERE 
CASE 
    WHEN status="ST1" THEN (time + INTERVAL 60 SECOND) > NOW() 
    WHEN status="ST2" THEN (time + INTERVAL 120 SECOND) > NOW() 
    WHEN status="ST3" THEN (time + INTERVAL 180 SECOND) > NOW() 
END CASE