postgresql – 当串联两列时,如何在字符串agg中添加顺序

发布时间：2020-12-13 15:55:25 所属栏目：百科来源：网络整理

导读：SELECT string_agg( distinct a || '-' || b,',' ORDER BY a,b) FROM table; 上面的sql给出了错误 ERROR: in an aggregate with DISTINCT,ORDER BY expressions must appear in argument list 解决方法对于 the documentation： If DISTINCT is specified i

SELECT string_agg( distinct a || '-' || b,',' ORDER BY a,b) 
FROM table;

上面的sql给出了错误

ERROR: in an aggregate with DISTINCT,ORDER BY expressions must appear in argument list

解决方法

对于 the documentation：

If DISTINCT is specified in addition to an order_by_clause,then all the ORDER BY expressions must match regular arguments of the aggregate; that is,you cannot sort on an expression that is not included in the DISTINCT list.

所以试试吧

select string_agg(distinct a || '-' || b,' order by a || '-' || b)
from a_table;

或在派生表中使用distinct：

select string_agg(a || '-' || b,' order by a,b)
from (
    select distinct a,b
    from a_table
    ) s;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!