mySQL 条件选择? [英] mySQL conditional select?

问题描述

表: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 其中:

Now I want to select id of 1 row from the table where:

如果 status 是 ST1: (time + 60) > $now

If status is ST1: (time + 60) > $now

如果 status 是 ST2: (time + 120) > $now

If status is ST2: (time + 120) > $now

如果 status 是 ST3: (time + 180) > $now

If status is ST3: (time + 180) > $now

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

So that if the row has the column status = "ST1" it checks if the column time + 60 is greater than $now which is the current timestamp, and so on.

推荐答案

只需使用基本逻辑:

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 -- 或者更好一个参数——如果你有充分的理由从应用程序传入时间.)

I'm assuming that (time + xx) > $now refers to seconds. Also, I encourage you to use the database time, rather than passing it in. (You can, of course, replace now() with $now -- or better yet a parameter -- if you have good reasons for passing the time in from the application.)

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

To be honest, I might put this in a derived table:

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;

这篇关于mySQL 条件选择?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！