我的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
更多推荐
保留string
发布评论