sqlite - 如何通过 UNION 重用表？

Question

我正在尝试重用 SQLite 中的表。我的尝试如下:

SELECT
    Partials.e_sentence
FROM
        (SELECT 
            e_sentence, _id
        FROM 
            Pair 
                JOIN PairCategories
                    ON
                        _id=PairId AND CategoryId=53

        UNION

        SELECT 
            e_sentence, _id
        FROM 
            Pair
        WHERE
            e_sentence LIKE '%' || 'how often' || '%'
        GROUP BY 
            e_sentence)
    AS Parents JOIN Partials
        ON Parents._id=ParentId

UNION

SELECT
    e_sentence
FROM
    Parents

我想要完成的关键部分在底部，我尝试对上一个语句中创建的表进行 UNION。有没有办法在 SQLite 中执行此操作，或者我是否被迫重复在 UNION 的前半部分创建 Parent 表的查询？

Answer 1

在 SQLite 3.8.3 或更高版本中，您可以使用 common table expression :

WITH Parents AS (
    SELECT e_sentence, _id
    FROM Pair
    JOIN PairCategories
    ...
)
SELECT Partials.e_sentence
FROM Parents
JOIN Partials ON Parents._id = ParentId
UNION
SELECT e_sentence
FROM Parents;

如果您使用的是较旧的 SQLite(可能是因为您使用的是 older Android )，则可以创建 view对于子查询:

CREATE VIEW Parents AS
    SELECT e_sentence, _id
    FROM Pair
    JOIN PairCategories
    ...;

SELECT Partials.e_sentence
FROM Parents
JOIN Partials ON Parents._id = ParentId
UNION
SELECT e_sentence
FROM Parents;

如果您不想将此 View 永久保留在数据库中，可以将其设为临时 View (CREATE TEMPORARY VIEW ...)，以便它在当前数据库连接之外不可用，或者作为最后的手段，您可以在要使用 Parent 的任何位置插入子查询:

SELECT Partials.e_sentence
FROM (SELECT ...) AS Parents
JOIN Partials ON Parents._id = ParentId
UNION
SELECT e_sentence
FROM (SELECT ...) AS Parents;