MySQL查询字段存在于另一表的去重聚合关联记录方法

基础信息 #

表1：land_information #

表2：sheets #

预期输出要求 #

校验sheets表的title_number是否存在于land_information表中，相同title_number对应的owner、case_no用逗号拼接，结果去重，最终输出如下：

实现方案 #

原SQL仅做了两表关联，未做聚合、去重处理，因此无法得到预期结果。核心实现逻辑分为三步：

• 先对land_information按title_number分组，将同title下的owner、case_no拼接为单条记录
• 内连接sheets表，自动过滤掉NULL、Unavailable这类在land_information中无匹配的title值
• 对关联结果按title_number分组，取每个title在sheets中最小的id，实现去重效果

不同数据库的字符串聚合函数有差异，对应SQL如下：

MySQL #

SELECT 
  MIN(s.id) AS id,
  li.title_number,
  li.owner,
  li.case_no
FROM sheets s
INNER JOIN (
  SELECT 
    title_number,
    GROUP_CONCAT(owner SEPARATOR ', ') AS owner,
    GROUP_CONCAT(case_no SEPARATOR ', ') AS case_no
  FROM land_information
  GROUP BY title_number
) li ON s.title_number = li.title_number
GROUP BY li.title_number
ORDER BY id;

PostgreSQL #

SELECT 
  MIN(s.id) AS id,
  li.title_number,
  li.owner,
  li.case_no
FROM sheets s
INNER JOIN (
  SELECT 
    title_number,
    STRING_AGG(owner, ', ') AS owner,
    STRING_AGG(case_no::text, ', ') AS case_no
  FROM land_information
  GROUP BY title_number
) li ON s.title_number = li.title_number
GROUP BY li.title_number, li.owner, li.case_no
ORDER BY id;

SQL Server #

SELECT 
  MIN(s.id) AS id,
  li.title_number,
  li.owner,
  li.case_no
FROM sheets s
INNER JOIN (
  SELECT 
    title_number,
    STRING_AGG(owner, ', ') AS owner,
    STRING_AGG(CAST(case_no AS VARCHAR(20)), ', ') AS case_no
  FROM land_information
  GROUP BY title_number
) li ON s.title_number = li.title_number
GROUP BY li.title_number, li.owner, li.case_no
ORDER BY id;

结果验证 #

执行上述SQL后，会自动排除sheets中无匹配的title_number记录，重复的title_number仅保留最小id行，关联字段按要求拼接，完全匹配预期输出。

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