mysql - 选择一行匹配条件，如果没有，则选择第一个

Question

我有一个包含一些数据的表。看起来像这样:

MariaDB [lokatnik]> SELECT id_elixir, start_time FROM elixir ORDER BY start_time ASC;
+-----------+------------+
| id_elixir | start_time |
+-----------+------------+
|         3 | 00:00:00   |
|         1 | 12:30:00   |
|         2 | 13:30:00   |
+-----------+------------+
3 rows in set (0.00 sec)

现在，我需要精确选择一行匹配时间条件 - 比如说 - start_time > some_time。

例如:

• 当 some_time 为 10:00 时，那么我需要 id_elixir = 1 的行(第一行时间超过 10:00)，
• 当 some_time 为 13:00 时，那么我需要 id_elixir = 2 的行(如上所示)，
• 当 some_time 为 14:00 时，我需要 id_elixir = 3 的行。

如何使用 SQL(和 MySQL/MariaDB DBMS)获取这一行？

Answer 1

也许，这样的东西适合你:

SELECT
  *
FROM
  elixir
WHERE
  CASE
    WHEN start_time BETWEEN CAST('10:00:00' AS TIME) AND CAST('13:00:00' AS
      TIME)
    THEN id =1
    WHEN start_time BETWEEN CAST('13:00:00' AS TIME) AND CAST('14:00:00' AS
      TIME)
    THEN id         =2
    WHEN start_time > CAST('14:00:00' AS TIME)
    THEN id         =3
    ELSE true

但您实际上可以使用简单的 bool 运算符来检查:

SELECT
  *
FROM
  elixir
WHERE
  (
    start_time BETWEEN CAST('10:00:00' AS TIME) AND CAST('13:00:00' AS TIME)
  AND id =1
  )
OR
  (
    start_time BETWEEN CAST('13:00:00' AS TIME) AND CAST('14:00:00' AS TIME)
  AND id =2
  )
OR
  (
    start_time > CAST('14:00:00' AS TIME)
  AND id       =3