sql - 没有嵌套查询的 listagg 中的不同值

Question

我正在尝试聚合一列:

SELECT LISTAGG(name, ';  ') WITHIN GROUP (ORDER BY name)
FROM tbl_a
INNER JOIN tbl_b ON tbl_a.foo = tbl_b.foo
WHERE tbl_a.id = 12345

这很好用，但我想删除重复项，所以我执行嵌套查询来过滤不同的查询。

SELECT LISTAGG(name, ';  ') WITHIN GROUP (ORDER BY name)
FROM
(
    SELECT DISTINCT tbl_a.name
    FROM tbl_a
    INNER JOIN tbl_b ON tbl_a.foo = tbl_b.foo
    WHERE tbl_a.id = 12345
)

然后我想把它放在另一个查询中，这样我就可以过滤变量值，而不是常量 12345:

SELECT
tbl_c.bar,
(
    SELECT LISTAGG(name, ';  ') WITHIN GROUP (ORDER BY name)
    FROM
    (
        SELECT DISTINCT tbl_a.name
        FROM tbl_a
        INNER JOIN tbl_b ON tbl_a.foo = tbl_b.foo
        WHERE tbl_a.id = tbl_c.bar
    )
) as names
FROM tbl_c;
/* gets complicated, C is joined with other tables and stuff */

因为 tbl_c.bar 嵌套了两次，所以显示为无效标识符。因此，这种方法是不可能的。

有什么方法可以做 listagg，丢弃重复但不嵌套吗？

我希望将结果聚合到一个单元格中，例如名称 1；姓名2； name3

---

我根本不关心性能。不过，可读性会很好。

Answer 1

这是一个相当讨厌的解决方案:

SELECT tbl_c.bar,
       (SELECT LISTAGG(name, ';  ') WITHIN GROUP (ORDER BY name)
        FROM (SELECT tbla.id, tbl_a.name
              FROM tbl_a INNER JOIN
                   tbl_b
                   ON tbl_a.foo = tbl_b.foo
              GROUP BY tbl_a.id
             ) x
        WHERE x.id = tbl_c.bar
       ) as names
FROM tbl_c;