为何GROUP BY name未生效？无法合并同name的department_id数组

表结构与数据 #

表attr包含以下字段：

id INT
name VARCHAR
department_id INT

现有数据：

id | name | department_id
1  | TEST | 1
2  | TEST | 2

需求与问题 #

期望查询时合并相同name对应的department_id为数组，仅返回一条name为TEST的结果，但执行以下SQL未达到预期：

SELECT 
id, 
name, 
ARRAY_AGG(department_id) as department_ids
FROM attr 
WHERE tenant_id = $1 AND department_id = ANY($2) 
GROUP BY name, id LIMIT 100;

执行结果：

[ 
    {
        "id": 46,
        "name": "TEST",
        "department_ids": [
            1
        ]
    },
    {
        "id": 47,
        "name": "TEST",
        "department_ids": [
            2
        ]
    },
]

原因分析 #

你当前的GROUP BY同时指定了name和id，而id是每条数据的唯一标识（INT类型通常作为主键或唯一值），这会让SQL将每个不同的id视为独立分组。即使name相同，只要id不同，就会被分成两个独立的组，自然无法合并department_id。

解决方案 #

方案1：不需要返回id字段 #

直接按name分组即可：

SELECT 
name, 
ARRAY_AGG(department_id) as department_ids
FROM attr 
WHERE tenant_id = $1 AND department_id = ANY($2) 
GROUP BY name LIMIT 100;

方案2：必须返回id字段 #

如果业务需要返回id，需通过聚合函数指定要返回的id（比如取最小id、最大id），确保每个name仅对应一个id值：

SELECT 
MIN(id) as id, -- 可根据需求替换为MAX(id)
name, 
ARRAY_AGG(department_id) as department_ids
FROM attr 
WHERE tenant_id = $1 AND department_id = ANY($2) 
GROUP BY name LIMIT 100;

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