mysql - 如何创建正确的查询？

Question

我正在尝试编写一个查询:

SELECT id FROM users WHERE status = 3

但如果此示例返回空响应，那么我需要改为在 status = 4 处选择 id，如果它再次返回空，则在 status = 5 处.

如何编写一个查询来解决这个问题？

Answer 1

我想你只是想要:

SELECT id
FROM users 
WHERE status >= 3
ORDER BY status asc
LIMIT 1;

如果你想要多个用户:

SELECT u.id
FROM users u
WHERE u.status = (SELECT MIN(u2.status)
                  FROM users u2
                  WHERE u2.status >= 3
                 );

如果你有一个固定列表要测试，你也可以使用:

select u.id
from users u
where u.status = 3
union all
select u.id
from users u
where u.status = 4 and
      not exists (select 1 from users u2 where u2.status in (3))
union all
select u.id
from users u
where u.status = 5 and
      not exists (select 1 from users u2 where u2.status in (3, 4));