多对多关联查询

在 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,PostgreSQLORDER 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 XMLselect idpageDocuments resultTypecom.yourpkg.DocumentDTO WITH paged_docs AS ( SELECT id, title, create_time FROM documents where if testkeyword ! 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 /select3. Service 层处理可选转为 List如果业务需要ListString而非字符串可在 Java 层简单转换documentDTO.setTags( dto.getTagList().isEmpty() ? Collections.emptyList() : Arrays.asList(dto.getTagList().split(,)) ); 四、性能优化建议优化点实施方案必建索引CREATE INDEX idx_doc_tags_doc ON doc_tags(doc_id);brCREATE 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_idt2.id WHERE dt2.doc_idd.id AND t2.name IN (Java))避免JOIN膨胀大数据量单文档标签 50 时string_agg内存开销上升建议改用应用层组装查文档 → 批量查标签 → JavaMapLong, ListString分组去重需求若关联表数据异常导致重复标签改用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]如果你需要带标签筛选 分页 聚合的完整 SQLJPA / MyBatis-Plus 的等价实现标签高亮或权重排序逻辑