sql - SQL查询优化:以一定条件进行输入，否则进行随机输入

Question

我想为满足特定条件的第一个值（最低ID）的SQLite 3创建一个SQL查询。如果没有这样的条目，我想获得一个随机行。因此，假设表中至少有一个条目，我总是希望仅返回一行。

考虑以下示例表：

CREATE TABLE `jukebox` (
    `id`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    `title` TEXT NOT NULL,
    `lastplayed`    INTEGER NOT NULL DEFAULT '0'
);

对于此示例，我想检索未播放的第一首歌曲（lastplayed = 0）。如果没有这样的歌曲，我想从表格中随机返回一行。

这是我到目前为止创建的。这是一个丑陋的怪物，我很确定它的性能不是很好。

SELECT * FROM (
    SELECT * FROM (SELECT * FROM jukebox WHERE lastplayed = 0 ORDER BY id DESC)
    UNION
    SELECT * FROM (SELECT * FROM jukebox ORDER BY RANDOM() LIMIT 1)
) LIMIT 1

我知道我可以省略 ORDER BY id DESC部分。我只是把它留在那里，所以很明显我期待什么结果。

奇怪的是，我不能在 LIMIT 1之后删除 RANDOM()，因为那样的话我不会得到随机结果。

有什么方法可以优化此查询？

Answer 1

您可以将您的版本表示为：

SELECT * 
FROM ((SELECT * FROM jukebox WHERE lastplayed = 0 ORDER BY id DESC)
      UNION ALL
      (SELECT * FROM jukebox ORDER BY RANDOM() LIMIT 1)
      )
     )
ORDER BY lastplayed
LIMIT 1;

或者，没有 UNION ALL：

SELECT *
FROM jukebox
ORDER BY (CASE WHEN lastplayed = 0 THEN 1 ELSE 2 END),
         (CASE WHEN lastplayed = 0 THEN id END),
         RANDOM()
LIMIT 1;

但是，我认为这可能会更好：

(SELECT *
 FROM jukebox
 WHERE lastplayed = 0
 ORDER BY id DESC
 LIMIT 1
)
UNION ALL
(SELECT *
 FROM jukebox
 WHERE NOT EXISTS (SELECT 1 FROM jukebox WHERE lastplayed = 0)
 ORDER BY RANDOM()
 LIMIT 1
)

当第一个子查询已经具有匹配项时，应避免排序。