postgresql - Postgres string_agg 函数未被识别为聚合函数

Question

我正在尝试运行此查询

SELECT  u.*, string_agg(CAST(uar.roleid AS VARCHAR(100)), ',') AS roleids, string_agg(CAST(r.role AS VARCHAR(100)), ',') AS systemroles
FROM idpro.users AS u 
INNER JOIN  idpro.userapplicationroles AS uar ON u.id = uar.userid 
INNER JOIN  idpro.roles AS r ON r.id = uar.roleid 
GROUP BY u.id, uar.applicationid
HAVING u.organizationid = '77777777-f892-4f4a-8328-c31df32bd6ba'
    AND uar.applicationid = 'd88fbf05-c048-4697-8bf3-036f39897183'
    AND (u.statusid = '7f9f0b75-44b7-4216-bf2a-03abc47dcff8') 
    AND uar.roleid IN ('cc9ada1c-fa21-400b-be98-c563ebb65a9c','de087148-4788-43da-89e2-dd7dff097735');

但是，我收到一条错误消息，指出

ERROR:  column "uar.roleid" must appear in the GROUP BY clause or be used in an aggregate function
LINE 9:  AND uar.roleid IN ('cc9ada1c-fa21-400b-be98-c563ebb65a9c','...

string_agg() 是一个聚合函数，不是吗？我的意图(如果不是很明显的话)是返回每个用户记录，其中包含以逗号分隔的列表中的角色 ID 和角色名称。如果我做错了一切，你能给我指出正确的方向吗？

Answer 1

您正在过滤数据，因此需要 WHERE 子句。这tutorial值得一读。

SELECT  u.*, 
        string_agg(CAST(uar.roleid AS VARCHAR(100)), ',') AS roleids,
        string_agg(CAST(r.role AS VARCHAR(100)), ',') AS systemroles
FROM idpro.users AS u 
INNER JOIN  idpro.userapplicationroles AS uar ON u.id = uar.userid 
INNER JOIN  idpro.roles AS r ON r.id = uar.roleid 
WHERE u.organizationid = '77777777-f892-4f4a-8328-c31df32bd6ba'
    AND uar.applicationid = 'd88fbf05-c048-4697-8bf3-036f39897183'
    AND (u.statusid = '7f9f0b75-44b7-4216-bf2a-03abc47dcff8') 
    AND uar.roleid IN ('cc9ada1c-fa21-400b-be98-c563ebb65a9c','de087148-4788-43da-89e2-dd7dff097735');
GROUP BY u.id, uar.applicationid

HAVING 子句有助于过滤聚合值或组。

由于您是按 u.id 分组的，因此您拥有的表主键 access to every column u 表。您可以使用 where 子句或 having 子句。

对于 uar.applicationid，它是 group by 的一部分，因此您也可以使用 where 或 having 。

uar.roleid 不是 group by 子句的一部分，因此要在 having 子句中使用，您必须考虑聚合值。

以下示例过滤掉聚合 长度超过 10 个字符的行。

HAVING length(string_agg(CAST(uar.roleid AS VARCHAR(100)), ',')) > 10

在数字字段上更常见的用法是过滤掉聚合行数是否小于阈值(having count(*) > 2)或某种总和( 总和(vacation_days) > 21)