如何编写SQL语句按X、Y、Z列去重并获取Column_A的首个值

现有数据表 TABLE：

执行以下SQL后，能得到分组后的X、Y、Z字段：

SELECT DISTINCT
       TABLE.Column_X,
       TABLE.Column_Y,
       TABLE.Column_Z
  FROM TABLE

返回结果：

TOON CITY OFF
STAR CARS OFF

现在需要同时获取每个分组里任意一个Column_A的值（比如分组内首个出现的），预期结果如下：

TOON CITY OFF 1234
STAR CARS OFF 1235

实际场景中去重后有约50种分组值，仅需为其中4种获取Column_A即可，尝试过自连接取TOP 1、GROUP BY但未成功，求正确SQL语句。

以下按不同数据库给出可行写法，可根据你使用的数据库选择：

SQL Server 环境 #

方法1：窗口函数取每组第一条 #

用ROW_NUMBER()给每个分组内的行编号，取编号为1的行即可。如果不需要指定顺序，ORDER BY (SELECT NULL)会让数据库返回任意一条；如果要取最早出现的，替换成ORDER BY Column_A即可。

SELECT Column_X, Column_Y, Column_Z, Column_A
FROM (
    SELECT 
        Column_X, Column_Y, Column_Z, Column_A,
        ROW_NUMBER() OVER (PARTITION BY Column_X, Column_Y, Column_Z ORDER BY (SELECT NULL)) AS rn
    FROM TABLE
) t
WHERE rn = 1

方法2：GROUP BY 结合聚合函数（简化版） #

如果对Column_A的具体值没有要求，只要是分组内的任意值，直接用MIN()或MAX()聚合即可，写法更简洁：

SELECT 
    Column_X, Column_Y, Column_Z,
    MIN(Column_A) AS Column_A
FROM TABLE
GROUP BY Column_X, Column_Y, Column_Z

如果只需要其中4个分组，加WHERE条件筛选：

SELECT 
    Column_X, Column_Y, Column_Z,
    MIN(Column_A) AS Column_A
FROM TABLE
WHERE (Column_X, Column_Y, Column_Z) IN (
    ('TOON', 'CITY', 'OFF'),
    ('STAR', 'CARS', 'OFF'),
    -- 替换成你需要的另外两个分组
    ('XXX', 'YYY', 'ZZZ'),
    ('AAA', 'BBB', 'CCC')
)
GROUP BY Column_X, Column_Y, Column_Z

方法3：OUTER APPLY 关联取每组第一条 #

SELECT DISTINCT
    t.Column_X, t.Column_Y, t.Column_Z,
    a.Column_A
FROM TABLE t
OUTER APPLY (
    SELECT TOP 1 Column_A
    FROM TABLE
    WHERE Column_X = t.Column_X 
      AND Column_Y = t.Column_Y 
      AND Column_Z = t.Column_Z
) a

MySQL 环境 #

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

写法和SQL Server类似：

SELECT Column_X, Column_Y, Column_Z, Column_A
FROM (
    SELECT 
        Column_X, Column_Y, Column_Z, Column_A,
        ROW_NUMBER() OVER (PARTITION BY Column_X, Column_Y, Column_Z ORDER BY NULL) AS rn
    FROM TABLE
) t
WHERE rn = 1

5.x 版本（无窗口函数） #

用GROUP_CONCAT拼接后取第一个值：

SELECT 
    Column_X, Column_Y, Column_Z,
    SUBSTRING_INDEX(GROUP_CONCAT(Column_A ORDER BY Column_A), ',', 1) AS Column_A
FROM TABLE
GROUP BY Column_X, Column_Y, Column_Z

PostgreSQL 环境 #

SELECT Column_X, Column_Y, Column_Z, Column_A
FROM (
    SELECT 
        Column_X, Column_Y, Column_Z, Column_A,
        ROW_NUMBER() OVER (PARTITION BY Column_X, Column_Y, Column_Z ORDER BY random()) AS rn
    FROM TABLE
) t
WHERE rn = 1

ORDER BY random()会随机取分组内的一个Column_A，如果要固定取最小/最大的，换成ORDER BY Column_A即可。

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