如何通过SQL高效查询多对多关系返回集合-实体结构的响应

最优实现方案（PostgreSQL专属，直接返回符合结构的数据） #

你用的是PostgreSQL数据库（语法里的ENUM类型、GENERATED ALWAYS AS IDENTITY都是PG特性），可以直接用PG内置的JSON聚合函数，单次查询就能得到完全符合你要求的结构，完全避免N+1查询问题：

SELECT
    t.id,
    t.name,
    t.updated_at,
    t.status,
    -- 聚合分类为JSON数组，无分类时返回空数组
    COALESCE(json_agg(
        json_build_object(
            'id', c.id,
            'name', c.name,
            'updated_at', c.updated_at
        )
    ) FILTER (WHERE c.id IS NOT NULL), '[]') AS categories
FROM tasks t
-- 左连保证没有分类的任务也不会被过滤
LEFT JOIN tasks_categories tc ON t.id = tc.tasks_id
LEFT JOIN categories c ON tc.categories_id = c.id
-- 按任务维度分组
GROUP BY t.id, t.name, t.updated_at, t.status;

查询结果里的categories字段已经是标准JSON数组，直接序列化返回给前端即可，不需要额外处理。

通用兼容方案（适配所有数据库） #

如果你用的不是PG，或者不想在SQL里处理JSON，也可以单次查询出所有关联数据，在服务端代码里做聚合，性能也远高于N+1查询：

1. 执行关联查询拿到所有任务+分类的平铺数据：

SELECT
    t.id AS task_id,
    t.name AS task_name,
    t.updated_at AS task_updated_at,
    t.status,
    c.id AS cate_id,
    c.name AS cate_name,
    c.updated_at AS cate_updated_at
FROM tasks t
LEFT JOIN tasks_categories tc ON t.id = tc.tasks_id
LEFT JOIN categories c ON tc.categories_id = c.id
ORDER BY t.id;

2. 在代码里按task_id分组，把同一个任务的分类数据组装成数组即可。

性能优化建议 #

不需要让客户端单独请求分类数据，反而会增加额外的网络开销，服务端一次性返回即可。只要给关联表加两个索引，即使数据量很大查询也不会有性能问题：

CREATE INDEX idx_tasks_categories_tasks_id ON tasks_categories(tasks_id);
CREATE INDEX idx_tasks_categories_categories_id ON tasks_categories(categories_id);

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