保留string

本文介绍了保留string_agg内部不同的顺序的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我的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代表国家公园，其中包括两个城市：圣加布里埃尔·达·卡乔埃拉和圣伊莎贝尔·里约内格罗。因此，它出现了两次。

which is a hierarchy of places. "PARNA" stands for "National Park", and this one covers two cities: São Gabriel da Cachoeira and Santa Isabel do Rio Negro. Thus it's appearing twice.

如果我将最后一行更改为

If I change the last line for

select string_agg(nome,', ') from locpais

我得到

PARNA Pico da Neblina，PARNA Pico da Neblina，圣加布里埃尔da Cachoeira，Santa Isabel do Rio Negro，RIO NEGRO，NORTE AMAZONENSE， Amazonas，Norte，Brasil

"PARNA Pico da Neblina, PARNA Pico da Neblina, São Gabriel da Cachoeira, Santa Isabel do Rio Negro, RIO NEGRO, NORTE AMAZONENSE, Amazonas, Norte, Brasil"

除了双打 PARNA Pico da Neblina外，其他都差不多。所以我尝试了：

Which is almost fine, except for the double "PARNA Pico da Neblina". So I tried:

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

，但现在我得到了

Amazonas，Brasil，诺特，诺特阿马松嫩，帕尔纳·皮科·达·内布利纳，里约 NEGRO，圣伊莎贝尔·里约·内格罗，圣加布里埃尔·达卡乔埃拉

"Amazonas, Brasil, Norte, NORTE AMAZONENSE, PARNA Pico da Neblina, RIO NEGRO, Santa Isabel do Rio Negro, São Gabriel da Cachoeira"

哪个故障。我正在尝试在 string_agg 内添加 by 的订单，但仍无法使它工作。在此处给出了表的定义。

Which is out of order. I'm trying to add an order by inside the string_agg, but couldn't make it work yet. The definition of the tables were given here.

推荐答案

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