如何将两个含相同关联与条件的SQL查询合并为单一查询？

我需要将以下两个查询的结果合并到一个查询中，二者使用相同的表关联和过滤条件，希望执行单一查询后得到包含sets列表和setCountByYear统计数据的JSON结果。

原查询1：获取分页的乐高套装列表 #

SELECT 
    sets.set_num, sets.name AS set_name, sets.year, sets.theme_id, 
    sets.num_parts, themes.name AS theme_name 
FROM 
    sets
INNER JOIN 
    themes ON sets.theme_id = themes.id 
WHERE 
    (sets.year IS NULL OR sets.year LIKE '%' || :set_year || '%') 
    AND (sets.name IS NULL OR sets.name LIKE '%' || :set_name || '%') 
    AND (themes.name IS NULL OR themes.name LIKE '%' || :theme_name || '%') 
ORDER BY 
    set_num 
LIMIT :limit OFFSET :offset

原查询2：按年份统计分页后的套装数量 #

SELECT 
    COUNT(a.set_num) AS count, a.year AS key 
FROM 
    (SELECT sets.set_num, sets.year 
     FROM sets 
     INNER JOIN themes ON sets.theme_id = themes.id 
     WHERE (sets.year IS NULL OR sets.year LIKE '%' || :set_year || '%') 
       AND (sets.name IS NULL OR sets.name LIKE '%' || :set_name || '%') 
       AND (themes.name IS NULL OR themes.name LIKE '%' || :theme_name || '%') 
     LIMIT :limit OFFSET :offset) AS a 
GROUP BY 
    a.year

期望的输出格式 #

{    
    "sets": [
        {
            "num": "21162-1",
            "name": "The Taiga Adventure",
            "year": 2020,
            "themeId": 577,
            "themeName": "Minecraft",
            "numParts": 74
        }
    ],
    "setCountByYear": [
        {
            "key": "2020",
            "count": 1
        }
    ]
}

表结构 #

sets表 #

CREATE TABLE sets 
(
    set_num varchar(16) PRIMARY KEY,
    name varchar(128),
    year smallint,
    theme_id smallint,
    num_parts int,
    FOREIGN KEY(theme_id) REFERENCES themes(id)
);

themes表 #

CREATE TABLE themes 
(
    id smallint PRIMARY KEY,
    name varchar(64),
    parent_id smallint
);

示例数据 #

sets表示例数据 #

themes表示例数据 #

解决方案 #

方法1：使用CTE+JSON函数直接生成目标结构 #

通过公共表表达式（CTE）复用过滤后的分页数据集，再用PostgreSQL的JSON函数直接构造符合要求的JSON结果，仅需执行一次查询：

WITH filtered_sets AS (
    SELECT 
        sets.set_num, sets.name AS set_name, sets.year, sets.theme_id, 
        sets.num_parts, themes.name AS theme_name 
    FROM 
        sets
    INNER JOIN 
        themes ON sets.theme_id = themes.id 
    WHERE 
        (sets.year IS NULL OR sets.year LIKE '%' || :set_year || '%') 
        AND (sets.name IS NULL OR sets.name LIKE '%' || :set_name || '%') 
        AND (themes.name IS NULL OR themes.name LIKE '%' || :theme_name || '%') 
    ORDER BY 
        set_num 
    LIMIT :limit OFFSET :offset
)
SELECT 
    json_build_object(
        'sets', json_agg(
            json_build_object(
                'num', set_num,
                'name', set_name,
                'year', year,
                'themeId', theme_id,
                'themeName', theme_name,
                'numParts', num_parts
            )
        ),
        'setCountByYear', (
            SELECT json_agg(
                json_build_object(
                    'key', year::text,
                    'count', count(set_num)
                )
            )
            FROM filtered_sets
            GROUP BY year
        )
    ) AS result
FROM filtered_sets
LIMIT 1;

方法2：CTE复用数据集，应用层合并结果 #

如果数据库对JSON函数支持有限，可以先通过CTE获取过滤后的分页数据，再分别查询两个结果集，最后在应用代码中组合成目标JSON：

-- 定义CTE复用过滤分页逻辑
WITH filtered_sets AS (
    SELECT 
        sets.set_num, sets.name AS set_name, sets.year, sets.theme_id, 
        sets.num_parts, themes.name AS theme_name 
    FROM 
        sets
    INNER JOIN 
        themes ON sets.theme_id = themes.id 
    WHERE 
        (sets.year IS NULL OR sets.year LIKE '%' || :set_year || '%') 
        AND (sets.name IS NULL OR sets.name LIKE '%' || :set_name || '%') 
        AND (themes.name IS NULL OR themes.name LIKE '%' || :theme_name || '%') 
    ORDER BY 
        set_num 
    LIMIT :limit OFFSET :offset
)
-- 查询套装列表
SELECT * FROM filtered_sets;

-- 单独查询年份统计
SELECT COUNT(set_num) AS count, year::text AS key FROM filtered_sets GROUP BY year;

两种方法都避免了重复执行过滤和分页逻辑，提升了查询效率。

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