如何让含LIMIT、RANDOM()和UNION的SQLite查询更易读？

问题背景 #

在SQLite中，无法直接在UNION的分支查询里使用LIMIT，以下写法不合法：

SELECT *
FROM Table
WHERE x
ORDER BY RANDOM()
LIMIT 5

UNION

SELECT *
FROM Table
WHERE y
ORDER BY RANDOM()
LIMIT 3

必须将每个分支用子查询包裹：

SELECT * FROM (
  SELECT *
  FROM Table
  WHERE x
  ORDER BY RANDOM()
  LIMIT 5
)

UNION

SELECT * FROM (
  SELECT *
  FROM Table
  WHERE y
  ORDER BY RANDOM()
  LIMIT 3
)

如果要对最终结果再次随机排序，直接在末尾加ORDER BY RANDOM()会报错Result: 1st ORDER BY term does not match any column in the result set，因此需要再嵌套一层子查询：

SELECT * FROM (
  SELECT * FROM (
    SELECT *
    FROM Table
    WHERE x
    ORDER BY RANDOM()
    LIMIT 5
  )

  UNION

  SELECT * FROM (
    SELECT *
    FROM Table
    WHERE y
    ORDER BY RANDOM()
    LIMIT 3
  )
) ORDER BY RANDOM()

实际场景与表结构 #

我的需求是：从CorrectCaption关联Caption中随机选取指定数量的正确标题，再从Caption中随机选取指定数量的非当前Meme的标题，最后打乱两部分结果。原始查询如下：

SELECT
    *
FROM
    (
        SELECT
            *
        FROM
            (
                SELECT
                    Caption.id,
                    caption
                FROM
                    CorrectCaption
                    JOIN Caption ON CorrectCaption.idCaption = Caption.id
                WHERE
                    idMeme = @idMeme
                ORDER BY
                    RANDOM()
                LIMIT
                    @numCorrectCaptions
            )
        UNION
        SELECT
            *
        FROM
            (
                SELECT
                    Caption.id,
                    caption
                FROM
                    Caption
                    LEFT JOIN CorrectCaption ON CorrectCaption.idCaption = Caption.id
                WHERE
                    idMeme <> @idMeme
                ORDER BY
                    RANDOM()
                LIMIT
                    @numIncorrectCaptions
            )
    )
ORDER BY
    RANDOM();

涉及的表结构：

CREATE TABLE IF NOT EXISTS "CorrectCaption" (
    "id" INTEGER NOT NULL,
    "idCaption" INTEGER NOT NULL,
    "idMeme" INTEGER NOT NULL,
    PRIMARY KEY("id" AUTOINCREMENT),
    FOREIGN KEY("idMeme") REFERENCES "Meme"("id"),
    FOREIGN KEY("idCaption") REFERENCES "Caption"("id")
);

CREATE TABLE IF NOT EXISTS "Caption" (
    "id" INTEGER NOT NULL,
    "caption" TEXT NOT NULL UNIQUE,
    PRIMARY KEY("id" AUTOINCREMENT)
);

已尝试的CTE重构 #

我用CTE重构后可读性有所提升，但仍希望找到更简洁高效的写法：

WITH CorrectCaptions AS (
    SELECT
        Caption.id,
        caption
    FROM
        CorrectCaption
        JOIN Caption ON CorrectCaption.idCaption = Caption.id
    WHERE
        idMeme = 1
    ORDER BY
        RANDOM()
    LIMIT
        2
), IncorrectCaptions AS (
    SELECT
        Caption.id,
        caption
    FROM
        Caption
        LEFT JOIN CorrectCaption ON CorrectCaption.idCaption = Caption.id
    WHERE
        idMeme <> 1
    ORDER BY
        RANDOM()
    LIMIT
        5
)
SELECT
    *
FROM
    (
        SELECT
            *
        FROM
            CorrectCaptions
        UNION ALL
        SELECT
            *
        FROM
            IncorrectCaptions
    )
ORDER BY
    RANDOM();

优化方案 #

1. 简化最终排序的嵌套 #

SQLite中，CTE结果可以直接合并后排序，无需额外嵌套子查询，可简化为：

WITH CorrectCaptions AS (
    SELECT
        Caption.id,
        caption
    FROM
        CorrectCaption
        JOIN Caption ON CorrectCaption.idCaption = Caption.id
    WHERE
        idMeme = @idMeme
    ORDER BY
        RANDOM()
    LIMIT
        @numCorrectCaptions
), IncorrectCaptions AS (
    SELECT
        Caption.id,
        caption
    FROM
        Caption
        LEFT JOIN CorrectCaption ON CorrectCaption.idCaption = Caption.id
    WHERE
        idMeme <> @idMeme
    ORDER BY
        RANDOM()
    LIMIT
        @numIncorrectCaptions
)
SELECT * FROM CorrectCaptions
UNION ALL
SELECT * FROM IncorrectCaptions
ORDER BY RANDOM();

该写法保留了CTE的可读性，同时去掉了多余嵌套，且不会触发排序报错。

2. 优化错误标题的查询逻辑 #

原错误标题查询用LEFT JOIN，可改用NOT EXISTS让逻辑更直观，同时避免JOIN可能带来的冗余：

IncorrectCaptions AS (
    SELECT
        Caption.id,
        caption
    FROM
        Caption
    WHERE NOT EXISTS (
        SELECT 1 FROM CorrectCaption
        WHERE CorrectCaption.idCaption = Caption.id
          AND CorrectCaption.idMeme = @idMeme
    )
    ORDER BY
        RANDOM()
    LIMIT
        @numIncorrectCaptions
)

此写法直接排除属于当前Meme的正确标题，逻辑清晰且性能更优。

3. 保留UNION ALL替代UNION #

由于正确与错误标题的ID无交集（分属不同Meme），使用UNION ALL可避免重复检查，比UNION效率更高。

关于三次ORDER BY RANDOM() #

前两次随机排序是必须的，用于从两个集合中随机选取指定数量的记录；最后一次是为了打乱合并结果，符合需求无法省略。SQLite的RANDOM()函数性能足够，常规数据量下不会有明显问题。

内容的提问来源于stack exchange，提问作者glowl