MySQL5.7环境下通过无关联子查询生成多字段去重表的SQL问题

安全阿岳

2026-10-7

问题原因

你当前的SQL返回重复customer_id的核心原因是t1子查询直接拉取了所有FieldName = 'Customer Id'的记录，没有做去重处理，原数据里JobId为1和4的记录都对应C01，所以会多出重复行。你直接加distinct会丢失其他字段关联，是因为distinct作用于全部返回列，行号匹配逻辑被打乱导致的。

修改后的完整SQL

SET @row_number_one = 0;
SET @row_number_two = 0;
SET @row_number_three = 0;
SET @row_number_four = 0;

SELECT 
  COALESCE(t1.FieldValue, '') AS customer_id, 
  COALESCE(t4.FieldValue, '') AS plot_id,
  COALESCE(t2.FieldValue, '') AS order_number,
  COALESCE(t3.FieldValue, '') AS architect_id 
FROM (
  -- 先对客户ID去重，再生成行号，保证主表无重复客户
  SELECT FieldValue, (@row_number_one:=@row_number_one + 1) AS rnk
  FROM (
    SELECT DISTINCT FieldValue 
    FROM table1 
    WHERE FieldName = 'Customer Id'
  ) AS distinct_customer
) AS t1
LEFT JOIN (
  -- 过滤空的订单号，再生成行号
  SELECT FieldValue, (@row_number_two:=@row_number_two + 1) AS rnk
  FROM table1
  WHERE FieldName = 'Order Number' AND FieldValue <> ''
) AS t2 ON t1.rnk = t2.rnk
LEFT JOIN (
  -- 过滤空的建筑师ID，再生成行号
  SELECT FieldValue, (@row_number_three:=@row_number_three + 1) AS rnk
  FROM table1
  WHERE FieldName = 'Architect Id' AND FieldValue <> ''
) AS t3 ON t1.rnk = t3.rnk
LEFT JOIN (
  -- 过滤空的地块ID，再生成行号
  SELECT FieldValue, (@row_number_four:=@row_number_four + 1) AS rnk
  FROM table1
  WHERE FieldName = 'Plot Id' AND FieldValue <> ''
) AS t4 ON t1.rnk = t4.rnk;