多对多关联查询
在 PostgreSQL 中，使用多对多关联表查询文档并将多个标签合并为逗号分隔的字符串，标准且高效的做法是使用聚合函数string_agg()。
下面提供生产级完整方案，包含核心 SQL、分页避坑指南、Java/MyBatis 映射。
📦 一、核心 SQL 实现
SELECT d.id, d.title, d.create_time, -- 核心：聚合标签为逗号分隔字符串 COALESCE(string_agg(t.name, ',' ORDER BY t.id), '') AS tag_list FROM documents d LEFT JOIN doc_tags dt ON d.id = dt.doc_id LEFT JOIN tags t ON dt.tag_id = t.id GROUP BY d.id, d.title, d.create_time ORDER BY d.create_time DESC;📌关键说明：
| 函数/语法 | 作用 |
|---|---|
LEFT JOIN | 保证没有标签的文档也能查出（不会因INNER JOIN被过滤） |
string_agg(t.name, ',' ORDER BY t.id) | 将多行标签名拼接成"Java,Spring,PostgreSQL"，ORDER BY保证每次顺序一致 |
COALESCE(..., '') | 无标签时返回空字符串""而非NULL，方便前端处理 |
GROUP BY | 必须包含所有非聚合字段（PG 严格要求） |
⚠️ 二、生产环境分页正确写法（必看）
直接在上面 SQL 加LIMIT/OFFSET会触发分页陷阱：JOIN会先产生笛卡尔积再GROUP BY，导致LIMIT计算错误且性能骤降。
✅正确做法：先分页，再关联聚合
WITH paged_docs AS ( -- 1. 先按条件筛选并分页（仅取文档主表） SELECT id, title, create_time FROM documents WHERE title ILIKE '%搜索词%' ORDER BY create_time DESC LIMIT 10 OFFSET 0 ) -- 2. 仅对分页后的 10 条文档关联标签并聚合 SELECT pd.id, pd.title, pd.create_time, COALESCE(string_agg(t.name, ',' ORDER BY t.id), '') AS tag_list FROM paged_docs pd LEFT JOIN doc_tags dt ON pd.id = dt.doc_id LEFT JOIN tags t ON dt.tag_id = t.id GROUP BY pd.id, pd.title, pd.create_time;💡 此写法在万级数据下仍能保持O(1)分页性能，是互联网项目标准实践。
☕ 三、Java + MyBatis 映射示例
1. DTO 定义
@Data public class DocumentDTO { private Long id; private String title; private LocalDateTime createTime; private String tagList; // "Java,Spring,PostgreSQL" }2. Mapper XML
<select id="pageDocuments" resultType="com.yourpkg.DocumentDTO"> WITH paged_docs AS ( SELECT id, title, create_time FROM documents <where> <if test="keyword != null and keyword != ''"> AND title ILIKE CONCAT('%', #{keyword}, '%') </if> </where> ORDER BY create_time DESC LIMIT #{limit} OFFSET #{offset} ) SELECT pd.id, pd.title, pd.create_time, COALESCE(string_agg(t.name, ',' ORDER BY t.id), '') AS tag_list FROM paged_docs pd LEFT JOIN doc_tags dt ON pd.id = dt.doc_id LEFT JOIN tags t ON dt.tag_id = t.id GROUP BY pd.id, pd.title, pd.create_time </select>3. Service 层处理（可选：转为 List）
如果业务需要List<String>而非字符串，可在 Java 层简单转换：
documentDTO.setTags( dto.getTagList().isEmpty() ? Collections.emptyList() : Arrays.asList(dto.getTagList().split(",")) );🚀 四、性能优化建议
| 优化点 | 实施方案 |
|---|---|
| 必建索引 | CREATE INDEX idx_doc_tags_doc ON doc_tags(doc_id);<br>CREATE INDEX idx_doc_tags_tag ON doc_tags(tag_id); |
| 标签过滤 | 若需“按标签筛选文档”，在WHERE中加AND EXISTS (SELECT 1 FROM doc_tags dt2 JOIN tags t2 ON dt2.tag_id=t2.id WHERE dt2.doc_id=d.id AND t2.name IN ('Java'))，避免JOIN膨胀 |
| 大数据量 | 单文档标签 >50 时，string_agg内存开销上升，建议改用应用层组装（查文档 → 批量查标签 → JavaMap<Long, List<String>>分组） |
| 去重需求 | 若关联表数据异常导致重复标签，改用string_agg(DISTINCT t.name, ',' ORDER BY t.name) |
💡 扩展：如果想返回 JSON 数组而非逗号字符串？
PostgreSQL 原生支持，前端解析更方便：
-- 替换 string_agg 部分 COALESCE(json_agg(t.name ORDER BY t.id) FILTER (WHERE t.name IS NOT NULL), '[]'::json) AS tags_json -- 结果: ["Java","Spring"]如果你需要：
- 带标签筛选 + 分页 + 聚合的完整 SQL
- JPA / MyBatis-Plus 的等价实现
- 标签高亮或权重排序逻辑