PostgreSQL查询结果缺失指定科目名称问题排查求助

需求与背景 #

需要统计不同科目下与培训师关联的人员数量，预期返回格式为：group_id|subject(用户数);subject(用户数);...，其中group_id是培训师组ID，后续为各主科目名称及对应组内用户数。

查询复用dm_group表实现两个逻辑：

• 通过gr.entity_type='Group' AND gr.group_type='TRAINER'识别培训师，此时gr.name为培训师姓名
• 通过gr.entity_type='Discipline'获取用户的主科目，此时gr.name为主科目名称

原查询逻辑：筛选所有培训师ID → 通过dm_user_to_group表找到每个培训师下的用户 → 关联dm_group表获取这些用户的主科目并统计数量。

表结构 #

dm_group表 #

create table dm_group
(
    entity_type            varchar(15) not null,
    id                     bigserial
        primary key,
    description            text,
    name                   varchar(255),
    group_type             varchar(255));

dm_user_to_group表 #

create table dm_user_to_group
(
    group_id bigint
        constraint fk_gr_to_u
            references dm_group,
    user_id  bigint
        constraint fk_us_to_gr
            references dm_user);

原查询语句 #

WITH TeacherSubjects AS (
    SELECT
        tr.id AS group_id,
        COALESCE(gr.name, '') AS subject_name,
        COUNT(utg.user_id) AS num_students
    FROM
        dm_group tr
            LEFT JOIN dm_user_to_group utg ON tr.id = utg.group_id
            LEFT JOIN dm_group gr ON utg.user_id = gr.id AND gr.entity_type = 'Discipline'
    WHERE
            tr.group_type = 'TRAINER'
    GROUP BY
        tr.id,
        gr.name
)
SELECT
    ts.group_id,
    STRING_AGG(ts.subject_name || '(' || ts.num_students || ')', '; ' ORDER BY ts.subject_name) AS "Discipline Counts"
FROM
    TeacherSubjects ts
GROUP BY
    ts.group_id
ORDER BY
    ts.group_id;

问题现象 #

查询统计结果正确，但无法输出dm_group表中已存在的所有subject_name——仅会显示有用户关联的科目，无用户关联的科目不会出现在结果中。

原因分析 #

原查询的关联逻辑是从培训师出发，通过用户关联到对应科目，仅会统计那些有用户（且用户属于某培训师）的科目。如果某个科目没有任何用户，或者用户未被分配到任何培训师组，该科目就不会进入统计流程，自然不会出现在最终结果里。

解决方案 #

要输出dm_group中所有已存在的科目，需要先单独获取所有科目列表，再与培训师做全关联，最后统计每个培训师对应每个科目的用户数（无用户则为0）。修改后的查询如下：

WITH AllTrainers AS (
    -- 获取所有培训师
    SELECT id AS group_id
    FROM dm_group
    WHERE group_type = 'TRAINER'
),
AllSubjects AS (
    -- 获取dm_group中所有已存在的主科目
    SELECT name AS subject_name
    FROM dm_group
    WHERE entity_type = 'Discipline'
),
TrainerSubjectStats AS (
    SELECT
        tr.group_id,
        subj.subject_name,
        -- 统计该培训师下属于当前科目的用户数
        COUNT(DISTINCT utg.user_id) AS num_students
    FROM AllTrainers tr
    -- 让每个培训师关联所有科目，确保科目不遗漏
    CROSS JOIN AllSubjects subj
    LEFT JOIN dm_user_to_group utg 
        ON tr.group_id = utg.group_id
    LEFT JOIN dm_group user_subj 
        ON utg.user_id = user_subj.id 
        AND user_subj.entity_type = 'Discipline'
        AND user_subj.name = subj.subject_name
    GROUP BY tr.group_id, subj.subject_name
)
SELECT
    group_id,
    STRING_AGG(subject_name || '(' || num_students || ')', '; ' ORDER BY subject_name) AS "Discipline Counts"
FROM TrainerSubjectStats
GROUP BY group_id
ORDER BY group_id;

关键修改说明 #

1. AllSubjects CTE：单独提取dm_group中所有Discipline类型的科目，确保所有已存在的科目都被纳入统计范围。
2. CROSS JOIN：让每个培训师与所有科目形成关联对，保证每个培训师的结果中都会包含所有科目。
3. LEFT JOIN 统计用户数：通过左连接匹配培训师的用户及其所属科目，无匹配时统计数为0，不会丢失科目记录。

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