保持string_agg内部不同的顺序

Question

我的SQL函数：

with recursive locpais as (
    select l.id, l.nome, l.tipo tid, lp.pai
    from loc l
    left join locpai lp on lp.loc = l.id
    where l.id = 12554
    union
    select l.id, l.nome, l.tipo tid, lp.pai
    from loc l
    left join locpai lp on lp.loc = l.id
    join locpais p on (l.id = p.pai)
)
select * from locpais

给我

12554 |     PARNA Pico da Neblina | 9 | 1564
12554 |     PARNA Pico da Neblina | 9 | 1547
 1547 |  São Gabriel da Cachoeira | 8 | 1400
 1564 | Santa Isabel do Rio Negro | 8 | 1400
 1400 |                 RIO NEGRO | 7 |  908
  908 |          NORTE AMAZONENSE | 6 |  234
  234 |                  Amazonas | 5 |  229
  229 |                     Norte | 4 |   30
   30 |                    Brasil | 3 |

这是一个地方的等级制度。"PARNA“是”国家公园“的意思，这座公园包括两个城市:S da Cachoeira和Santa Isabel do Rio Ne格罗。因此它出现了两次。

如果我把最后一行改为

select string_agg(nome,', ') from locpais

我得到了

“帕纳·皮科·达·内布里亚、帕纳·皮科·达·内布里亚、圣·加布里埃尔·达卡希埃拉、圣伊莎贝尔·多里奥·内格罗、里约·内格罗、北亚马孙、亚马孙、北特、巴西”

这几乎是好的，除了双倍的“帕纳皮科达内布里亚”。所以我试着：

select string_agg(distinct nome, ', ') from locpais

但现在我得到了

“亚马孙、巴西、北方、北亚马逊、帕纳皮科达内布里亚、里约内格罗、圣伊莎贝尔做里约内格罗、圣加布里埃尔达卡西埃拉”

这是不正常的。我试图在order by中添加一个string_agg，但还无法使其工作。表的定义给出了这里。

Answer 1

select string_agg(nome,', ')
from (
    select distinct nome
    from locpais
    order by tid desc
) s

Answer 2

正如您已经发现的，如果不首先按照不同的表达式进行排序，就不能将DISTINCT和ORDER BY组合起来：

• 两者均未在总数中： 子句，则所有ORDER表达式都必须与聚合的常规参数匹配；也就是说，不能对未包含在不同列表中的表达式进行排序。
• 也不选： DISTINCT ON表达式必须按表达式匹配最左边的顺序。

但是，可以使用这样的方法

array_to_string(arry_uniq_stable(array_agg(nome ORDER BY tid DESC)), ', ') 

在函数arry_uniq_stable的帮助下，它删除数组w/o中的重复项，改变它的顺序，就像我在https://stackoverflow.com/a/42399297/5805552中给出的示例一样

请注意使用一个ORDER BY表达式，它实际上给出了一个确定的结果。使用您已经给出的示例，仅使用tid是不够的，因为有不同nome的重复值(8)。