保留string_agg内部不同的顺序 [英] Preserve the order of distinct inside 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

这篇关于保留string_agg内部不同的顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！