如何将两张表按指定字段Join并按另一字段分组查询得到预期结果

实现逻辑 #

1. 对Table1按Roll No.去重，获取每个学员唯一的姓名信息
2. 对Table2按Roll no.分组，将同个学员所有中心的报告内容拼接为单个字符串
3. 关联两个处理后的结果，固定输出Center ID为A即可

不同数据库实现代码 #

MySQL #

SELECT 
  t1.`Roll No.`,
  'A' AS `Center ID`,
  t2.report AS Report,
  t1.Name
FROM (
  SELECT DISTINCT `Roll No.`, Name FROM Table1
) t1
INNER JOIN (
  SELECT `Roll no.`, GROUP_CONCAT(Report SEPARATOR ',') AS report
  FROM Table2
  GROUP BY `Roll no.`
) t2 ON t1.`Roll No.` = t2.`Roll no.`;

PostgreSQL / SQL Server 2017及以上版本 #

SELECT 
  t1."Roll No.",
  'A' AS "Center ID",
  t2.report AS Report,
  t1.Name
FROM (
  SELECT DISTINCT "Roll No.", Name FROM Table1
) t1
INNER JOIN (
  SELECT "Roll no.", STRING_AGG(Report, ',') AS report
  FROM Table2
  GROUP BY "Roll no."
) t2 ON t1."Roll No." = t2."Roll no.";

Oracle #

SELECT 
  t1."Roll No.",
  'A' AS "Center ID",
  t2.report AS Report,
  t1.Name
FROM (
  SELECT DISTINCT "Roll No.", Name FROM Table1
) t1
INNER JOIN (
  SELECT "Roll no.", LISTAGG(Report, ',') WITHIN GROUP (ORDER BY "Center ID") AS report
  FROM Table2
  GROUP BY "Roll no."
) t2 ON t1."Roll No." = t2."Roll no.";

低版本SQL Server（2016及以下） #

SELECT 
  t1.[Roll No.],
  'A' AS [Center ID],
  STUFF((
    SELECT ',' + Report 
    FROM Table2 t2_sub 
    WHERE t2_sub.[Roll no.] = t1.[Roll No.] 
    FOR XML PATH(''), TYPE
  ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS Report,
  t1.Name
FROM (SELECT DISTINCT [Roll No.], Name FROM Table1) t1

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