如何合并分组筛选查询与成员统计查询？PostgreSQL报错求助

方案一：使用EXISTS过滤目标分组（推荐，更高效） #

直接在统计查询中通过EXISTS和NOT EXISTS筛选出符合条件的分组（指定租户分组+公共分组），同时完成成员统计：

SELECT 
    kg.id,
    kg.name, -- 可根据需求添加其他分组字段
    COUNT(ugm.user_id) AS member_count
FROM keycloak_group kg
LEFT JOIN user_group_membership ugm ON ugm.group_id = kg.id
WHERE 
    -- 匹配指定租户的分组（存在CompanyId属性且值为@tenantKey）
    EXISTS (
        SELECT 1 
        FROM group_attribute ga 
        WHERE ga.group_id = kg.id 
          AND ga.name = 'CompanyId' 
          AND ga.value = @tenantKey
    )
    -- 匹配公共分组（不存在CompanyId属性）
    OR NOT EXISTS (
        SELECT 1 
        FROM group_attribute ga 
        WHERE ga.group_id = kg.id 
          AND ga.name = 'CompanyId'
    )
GROUP BY kg.id, kg.name; -- PostgreSQL要求SELECT中所有非聚合字段必须在GROUP BY中

方案二：先筛选分组再统计成员 #

通过CTE先提取符合条件的分组，再关联用户组关系统计成员数：

WITH eligible_groups AS (
    -- 提取指定租户的分组
    SELECT kg.*
    FROM keycloak_group kg 
    JOIN group_attribute ga ON ga.group_id = kg.id AND ga.name = 'CompanyId'
    WHERE ga.value = @tenantKey
    -- 合并公共分组
    UNION
    SELECT kg.*
    FROM keycloak_group kg 
    LEFT JOIN group_attribute ga ON ga.group_id = kg.id AND ga.name = 'CompanyId'
    WHERE ga.id IS NULL
)
SELECT 
    eg.id,
    eg.name, -- 可添加其他分组字段
    COUNT(ugm.user_id) AS member_count
FROM eligible_groups eg
LEFT JOIN user_group_membership ugm ON ugm.group_id = eg.id
GROUP BY eg.id, eg.name;

原错误原因说明 #

你之前合并时出现column "ga.id" must appear in the GROUP BY clause错误，是因为原UNION查询返回了group_attribute表的列（如ga.id），而PostgreSQL要求SELECT语句中所有非聚合列必须出现在GROUP BY子句中。若强行添加ga.id到GROUP BY，会导致同一个分组因为ga.id的存在/缺失被拆分成两行，最终统计结果重复或不符合预期。

解决核心是：将分组筛选与成员统计分离，确保统计时只基于分组本身的字段（而非关联的属性表字段）。

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