如何编写SQL语句实现查询项目列表及逗号拼接的关联任务

1. 表结构说明 #

1.1 一对多关联场景（任务直接归属单个项目） #

• Projects表核心字段：project_id（主键）、project_name 等项目属性
• Tasks表核心字段：task_id（主键）、project_id（外键关联Projects表的project_id）、task_name 等任务属性

1.2 多对多关联场景（单个任务可归属多个项目） #

额外创建中间表Projects_Tasks，字段如下：

• project_id 外键关联Projects表
• task_id 外键关联Tasks表
• 联合主键为(project_id, task_id)避免重复关联

通用说明：所有语句使用LEFT JOIN保证无关联任务的项目也会出现在结果中，若仅需返回有任务的项目，替换为INNER JOIN即可。如果需要无关联任务时显示空字符串而非NULL，可将聚合函数部分包裹在COALESCE()中处理。

2. 各数据库实现语句 #

2.1 MySQL #

一对多场景

SELECT 
  p.project_id,
  p.project_name,
  GROUP_CONCAT(DISTINCT t.task_name ORDER BY t.task_id SEPARATOR ',') AS related_tasks
FROM Projects p
LEFT JOIN Tasks t ON p.project_id = t.project_id
GROUP BY p.project_id, p.project_name;

多对多场景

SELECT 
  p.project_id,
  p.project_name,
  GROUP_CONCAT(DISTINCT t.task_name ORDER BY t.task_id SEPARATOR ',') AS related_tasks
FROM Projects p
LEFT JOIN Projects_Tasks pt ON p.project_id = pt.project_id
LEFT JOIN Tasks t ON pt.task_id = t.task_id
GROUP BY p.project_id, p.project_name;

注意：MySQL默认GROUP_CONCAT长度限制为1024字节，任务过多时需要先执行SET group_concat_max_len = 102400;调整长度上限。

2.2 PostgreSQL & SQL Server #

一对多场景

SELECT 
  p.project_id,
  p.project_name,
  STRING_AGG(DISTINCT t.task_name, ',' ORDER BY t.task_id) AS related_tasks
FROM Projects p
LEFT JOIN Tasks t ON p.project_id = t.project_id
GROUP BY p.project_id, p.project_name;

多对多场景

SELECT 
  p.project_id,
  p.project_name,
  STRING_AGG(DISTINCT t.task_name, ',' ORDER BY t.task_id) AS related_tasks
FROM Projects p
LEFT JOIN Projects_Tasks pt ON p.project_id = pt.project_id
LEFT JOIN Tasks t ON pt.task_id = t.task_id
GROUP BY p.project_id, p.project_name;

2.3 Oracle #

一对多场景

SELECT 
  p.project_id,
  p.project_name,
  LISTAGG(DISTINCT t.task_name, ',') WITHIN GROUP (ORDER BY t.task_id) AS related_tasks
FROM Projects p
LEFT JOIN Tasks t ON p.project_id = t.project_id
GROUP BY p.project_id, p.project_name;

多对多场景

SELECT 
  p.project_id,
  p.project_name,
  LISTAGG(DISTINCT t.task_name, ',') WITHIN GROUP (ORDER BY t.task_id) AS related_tasks
FROM Projects p
LEFT JOIN Projects_Tasks pt ON p.project_id = pt.project_id
LEFT JOIN Tasks t ON pt.task_id = t.task_id
GROUP BY p.project_id, p.project_name;

注意：Oracle 12c及以上版本才支持LISTAGG函数的DISTINCT关键字，低版本需要先对关联的任务数据去重再执行聚合。

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