本文介绍如何在 PostgreSQL 中使用 string_agg() 与 COUNT() 结合，对教师（TRAINER）所带学生按其主学科（Discipline）进行分组计数，并以 学科(人数);学科(人数) 的格式聚合输出。
本文介绍如何在 postgresql 中使用 `string_agg()` 与 `count()` 结合，对教师（trainer）所带学生按其主学科（discipline）进行分组计数，并以 `学科(人数);学科(人数)` 的格式聚合输出。
要实现“每个教师 ID 对应其所有学生主学科及对应人数列表”的需求，关键在于两层聚合：内层按教师 ID 和学科名统计人数，外层将结果拼接为结构化字符串。原始查询失败的主要原因在于子查询逻辑混乱——错误地将 d.name 与 tr.name 关联、误用 FULL OUTER JOIN、且未正确关联用户与其主学科记录。
正确的解法是采用清晰的 JOIN 链路 + 子查询预聚合：
- 从
dm_group tr筛选出所有group_type = 'TRAINER'的教师记录； - 通过
dm_user_to_group ug关联其下属用户（学生）； - 再通过
dm_user u关联到dm_group d，以u.id = d.id且d.entity_type = 'Discipline'精准定位该用户的主学科名称（注意：此处假设用户 ID 与dm_group.id在学科记录中一致，符合题设“每个用户有至少一条entity_type='Discipline'的dm_group记录，且id对应其本人”）； - 在子查询中按
tr.id和d.name分组，用COUNT(DISTINCT d.id)统计每门学科的学生数（使用DISTINCT可规避重复关联风险）； - 外层使用
string_agg()将d.name || '(' || count || ')'按学科名排序后以'; '拼接。
以下是可直接运行的优化 SQL：
SELECT
id,
string_agg(dis_ct, '; ' ORDER BY dis_ct) AS "Discipline Counts"
FROM (
SELECT
tr.id,
d.name || '(' || COUNT(DISTINCT d.id) || ')' AS dis_ct
FROM dm_group tr
LEFT JOIN dm_user_to_group ug ON ug.group_id = tr.id
LEFT JOIN dm_user u ON u.id = ug.user_id
LEFT JOIN dm_group d
ON d.id = u.id AND d.entity_type = 'Discipline'
WHERE tr.group_type = 'TRAINER'
GROUP BY tr.id, d.name
) sub
GROUP BY id
ORDER BY id;✅ 关键说明与注意事项：
-
COUNT(DISTINCT d.id)是安全选择：即使某学生未配置主学科（d表无匹配行），d.id为NULL，COUNT(DISTINCT NULL)返回 0，且NULL值在GROUP BY中自动被排除，不会产生空括号项； -
无需
FILTER或CASE：因d.name在无匹配时为NULL，NULL || '(0)'结果仍为NULL，而string_agg()默认忽略NULL值，天然过滤掉无效学科； -
排序建议：外层
ORDER BY dis_ct按字符串字典序排列学科（如 “Math(3); Physics(5)”），若需按人数降序，可改用ORDER BY COUNT(DISTINCT d.id) DESC并在子查询中保留该字段； -
性能提示：确保
dm_group(group_type, id)、dm_user_to_group(group_id, user_id)及dm_group(entity_type, id)上存在合适索引，尤其dm_group(entity_type, id)对加速学科查找至关重要。
该方案结构清晰、语义明确，完美契合多层级关系聚合场景，是 PostgreSQL 中处理“分组→计数→格式化拼接”类需求的标准范式。
