如何将主表与存储元数据的另一张表关联合并且避免主数据重复

数据老陈

2026-9-30

多表一对多关联查询主表字段去重方案

你遇到的重复问题是INNER JOIN关联一对多关系的表时的正常表现，要实现主表字段仅首行显示，可通过以下方案实现：

MySQL 8.0+ 版本（支持窗口函数）

核心逻辑是通过ROW_NUMBER()窗口函数给同主表ID的元数据行生成序号，仅序号为1的行返回主表字段值：

USE test;
SELECT
  CASE WHEN mm.row_num = 1 THEN m.id ELSE NULL END AS id,
  CASE WHEN mm.row_num = 1 THEN m.body ELSE NULL END AS body,
  CASE WHEN mm.row_num = 1 THEN m.title ELSE NULL END AS title,
  CASE WHEN mm.row_num = 1 THEN m.`time` ELSE NULL END AS `time`,
  -- 输出所有元数据字段，可按需替换为指定字段
  mm.* 
FROM (
  SELECT 
    *,
    ROW_NUMBER() OVER (PARTITION BY ref ORDER BY id) AS row_num
  FROM main_meta
  WHERE ref = 1
) mm
INNER JOIN main m ON mm.ref = m.id;

可通过修改ORDER BY后的字段调整元数据的排序规则
不需要展示的元数据字段可直接替换mm.*为指定字段列表

MySQL 5.x 兼容版本

通过自定义变量实现序号计数功能：

USE test;
SELECT id, body, title, `time`, /* 按需填写需要保留的元数据字段，剔除temp_row */
FROM (
  SELECT
    CASE WHEN @row = 1 THEN m.id ELSE NULL END AS id,
    CASE WHEN @row = 1 THEN m.body ELSE NULL END AS body,
    CASE WHEN @row = 1 THEN m.title ELSE NULL END AS title,
    CASE WHEN @row = 1 THEN m.`time` ELSE NULL END AS `time`,
    mm.*,
    @row := @row + 1 AS temp_row
  FROM main_meta mm
  INNER JOIN main m ON mm.ref = m.id
  CROSS JOIN (SELECT @row := 1) init_var
  WHERE mm.ref = 1
  ORDER BY mm.id
) t;