如何从含重复列值的表中按NAME取单行并关联唯一值表？

原表数据 #

需求说明 #

要从上述存在重复COLUMN1（NAME）的表中，为每个NAME仅保留一行数据（ID Numb和DESCR任意取值即可），再与另一张NAME唯一的表做左连接，避免关联后出现重复行。

可行方案 #

1. 用DISTINCT直接去重 #

最简单的方式，直接提取唯一的NAME+ID+DESCR组合，适合不需要指定选取规则的场景：

SELECT DISTINCT COLUMN1, Column2, COLUMN3
FROM your_table;

关联时将结果作为子查询使用：

SELECT t2.*, t1.Column2, t1.COLUMN3
FROM unique_name_table t2
LEFT JOIN (
    SELECT DISTINCT COLUMN1, Column2, COLUMN3
    FROM your_table
) t1 ON t2.NAME = t1.COLUMN1;

2. 分组聚合（MIN/MAX） #

通过GROUP BY按NAME分组，用MIN或MAX随机选取一个ID和DESCR，确保每个NAME仅出现一次：

SELECT COLUMN1,
       MIN(Column2) AS ID_Numb,
       MIN(COLUMN3) AS DESCR
FROM your_table
GROUP BY COLUMN1;

关联写法：

SELECT t2.*, t1.ID_Numb, t1.DESCR
FROM unique_name_table t2
LEFT JOIN (
    SELECT COLUMN1,
           MIN(Column2) AS ID_Numb,
           MIN(COLUMN3) AS DESCR
    FROM your_table
    GROUP BY COLUMN1
) t1 ON t2.NAME = t1.COLUMN1;

3. 窗口函数ROW_NUMBER() #

给每个NAME的行分配序号，只取序号为1的行，如需固定选取规则可调整ORDER BY：

WITH ranked_data AS (
    SELECT COLUMN1, Column2, COLUMN3,
           ROW_NUMBER() OVER (PARTITION BY COLUMN1 ORDER BY (SELECT NULL)) AS rn
    FROM your_table
)
SELECT COLUMN1, Column2, COLUMN3
FROM ranked_data
WHERE rn = 1;

关联时嵌套进左连接：

WITH ranked_data AS (
    SELECT COLUMN1, Column2, COLUMN3,
           ROW_NUMBER() OVER (PARTITION BY COLUMN1 ORDER BY (SELECT NULL)) AS rn
    FROM your_table
)
SELECT t2.*, t1.Column2, t1.COLUMN3
FROM unique_name_table t2
LEFT JOIN (
    SELECT COLUMN1, Column2, COLUMN3
    FROM ranked_data
    WHERE rn = 1
) t1 ON t2.NAME = t1.COLUMN1;

提示：ORDER BY (SELECT NULL)是让数据库随机选一行，若想固定选取逻辑（比如ID最小的行），可替换为ORDER BY Column2。

4. 子查询取单条记录 #

不同数据库语法略有差异，示例如下：

• MySQL 用LIMIT：

SELECT COLUMN1, Column2, COLUMN3
FROM your_table t
WHERE (Column2, COLUMN3) = (
    SELECT Column2, COLUMN3
    FROM your_table
    WHERE COLUMN1 = t.COLUMN1
    LIMIT 1
);

• SQL Server 用TOP 1：

SELECT COLUMN1, Column2, COLUMN3
FROM your_table t
WHERE (Column2, COLUMN3) = (
    SELECT TOP 1 Column2, COLUMN3
    FROM your_table
    WHERE COLUMN1 = t.COLUMN1
);

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