如何在多UNION的SQL查询中按指定字段实现DISTINCT去重？

你的需求是基于Identification、Family_name、first_name这三列去重，忽略address_1的差异，以下是可行的实现方式：

方法1：GROUP BY 聚合（推荐） #

将所有UNION拼接的查询作为数据源，通过GROUP BY按目标三列分组，同时对address_1取任意值（比如取最小、最大或任意出现的，根据你的需求选择）：

SELECT
    Identification,
    Family_name,
    first_name,
    MIN(address_1) AS address_1 -- 或用MAX(address_1)，任选其一即可
FROM (
    SELECT
        A.ID AS Identification,
        B.Name AS Family_name,
        B.Firstname AS first_name,
        B.Address AS address_1
    FROM A 
    LEFT JOIN B ON B.ID = A.ID

    UNION 

    SELECT
        C.ID AS Identification,
        D.Name AS Family_name,
        D.Firstname AS first_name,
        D.Address AS address_1
    FROM C 
    LEFT JOIN D ON D.ID = C.ID

    -- 补充其他UNION分支
) AS combined_data
GROUP BY Identification, Family_name, first_name;

方法2：窗口函数去重 #

如果需要保留特定规则的address_1（比如最新的、符合特定条件的），可以用窗口函数ROW_NUMBER()标记分组内的行，然后筛选出第一行：

SELECT Identification, Family_name, first_name, address_1
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY Identification, Family_name, first_name 
            ORDER BY address_1 -- 可根据需求调整排序规则，比如按地址长度、关联时间等
        ) AS rn
    FROM (
        SELECT
            A.ID AS Identification,
            B.Name AS Family_name,
            B.Firstname AS first_name,
            B.Address AS address_1
        FROM A 
        LEFT JOIN B ON B.ID = A.ID

        UNION 

        SELECT
            C.ID AS Identification,
            D.Name AS Family_name,
            D.Firstname AS first_name,
            D.Address AS address_1
        FROM C 
        LEFT JOIN D ON D.ID = C.ID

        -- 补充其他UNION分支
    ) AS combined_data
) AS ranked_data
WHERE rn = 1;

关于你之前尝试的错误说明 #

1. DISTINCT (ID, name, firstname) 不是合法的SQL语法，DISTINCT会作用于所有选中的列，无法单独指定某几列去重；
2. 外层用SELECT DISTINCT Identification, Family_Name, First_Name和原查询UNION报错，是因为UNION要求前后查询的列数、数据类型完全匹配，原查询返回4列，外层查询返回3列，因此冲突。

内容的提问来源于stack exchange，提问作者Arçan