保留 string_agg 内部的不同顺序

Mysql函数：

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

gives me

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ão Gabriel da Cachoeira 和 Santa Isabel do Rio Negro。因此它出现了两次。

如果我将最后一行更改为

select string_agg(nome,', ') from locpais

I get

“帕纳皮科达内布利纳，帕纳皮科达内布利纳，圣加布里埃尔达 Cachoeira, Santa Isabel do Rio Negro, RIO NEGRO, NORTE AMAZONENSE, 亚马逊、北部、巴西”

除了双“PARNA Pico da Neblina”之外，这几乎没问题。所以我尝试：

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

但现在我明白了

“亚马逊、巴西、北部、NORTE AMAZONENSE、帕纳皮科达内布利纳、里约热内卢 NEGRO、Santa Isabel do Rio Negro、São Gabriel da Cachoeira”

这是不正常的。我正在尝试添加一个order by在 - 的里面string_agg，但还无法使其发挥作用。给出了表的定义here https://stackoverflow.com/q/35702246/1086511.

正如您所发现的，您不能合并DISTINCT and ORDER BY如果您不首先按不同的表达式排序：

• 总体而言：

  如果除了 order_by_clause 之外还指定了 DISTINCT，则所有 ORDER BY 表达式必须与聚合的常规参数匹配；也就是说，您无法对未包含在 DISTINCT 列表中的表达式进行排序。 https://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-AGGREGATES

• 也不在选择中：

  DISTINCT ON 表达式）必须与最左边的 ORDER BY 表达式匹配。 https://www.postgresql.org/docs/current/static/sql-select.html#SQL-DISTINCT

不过可以使用类似的东西

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

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

请小心使用ORDER BY实际上给出确定性结果的表达式。根据你给出的例子，tid单独是不够的，因为存在不同的重复值 (8)nome.