SQL聚合后过滤实现：筛选关联实体并保留全量聚合信息

针对你遇到的「筛选喜欢橙子的用户，但需保留该用户所有喜欢的水果聚合信息」的问题，推荐以下两种高效方案，避免CTE或INTERSECT带来的性能损耗：

示例基础DDL #

先定义场景用到的三张表（用户、水果、用户-水果关联表）：

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

CREATE TABLE fruits (
    fruit_id INT PRIMARY KEY,
    fruit_name VARCHAR(50) NOT NULL
);

CREATE TABLE user_favorites (
    user_id INT REFERENCES users(user_id),
    fruit_id INT REFERENCES fruits(fruit_id),
    PRIMARY KEY (user_id, fruit_id)
);

方案1：EXISTS半连接筛选用户（性能最优） #

利用EXISTS做存在性判断，先锁定喜欢橙子的用户，再对这些用户的所有关联水果做聚合。EXISTS是半连接逻辑，找到匹配记录就停止查询，不需要生成临时表，性能远优于CTE或INTERSECT：

SELECT
    u.user_id,
    u.username,
    -- 聚合用户所有喜欢的水果（示例用字符串拼接，可替换为其他聚合逻辑）
    STRING_AGG(f.fruit_name, ', ') AS all_favorite_fruits,
    COUNT(f.fruit_id) AS favorite_fruit_count
FROM users u
JOIN user_favorites uf ON u.user_id = uf.user_id
JOIN fruits f ON uf.fruit_id = f.fruit_id
-- 核心：仅筛选存在「喜欢橙子」记录的用户
WHERE EXISTS (
    SELECT 1
    FROM user_favorites uf2
    JOIN fruits f2 ON uf2.fruit_id = f2.fruit_id
    WHERE uf2.user_id = u.user_id
      AND f2.fruit_name = '橙子'
)
GROUP BY u.user_id, u.username;

方案2：窗口函数标记+聚合 #

如果需要更灵活的用户标记逻辑（比如同时判断多个条件），可以用窗口函数先给每个用户打「是否喜欢橙子」的标记，再过滤并聚合。这里用子查询替代CTE，避免额外的临时表开销：

SELECT
    user_id,
    username,
    STRING_AGG(fruit_name, ', ') AS all_favorite_fruits,
    COUNT(fruit_name) AS favorite_fruit_count
FROM (
    SELECT
        u.user_id,
        u.username,
        f.fruit_name,
        -- 窗口函数：按用户分组，标记是否喜欢橙子
        MAX(CASE WHEN f.fruit_name = '橙子' THEN 1 ELSE 0 END) OVER (PARTITION BY u.user_id) AS likes_orange
    FROM users u
    JOIN user_favorites uf ON u.user_id = uf.user_id
    JOIN fruits f ON uf.fruit_id = f.fruit_id
) AS user_fruit_data
WHERE likes_orange = 1
GROUP BY user_id, username;

性能优化补充 #

• 索引优化：给fruits(fruit_name, fruit_id)建立联合索引，user_favorites(fruit_id)建立单独索引，能大幅加快EXISTS子查询和窗口函数的判断速度。
• 避免INTERSECT：INTERSECT会做去重和排序，在多表关联场景下性能远不如EXISTS或窗口函数。
• 替换聚合逻辑：可以根据需求把STRING_AGG换成ARRAY_AGG（支持数组的数据库）或其他聚合函数，不影响核心筛选逻辑。

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