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，丢弃重复项但不嵌套？

我希望将结果聚合到单个单元格中，例如 name1; name2; 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;

Answer 2

此查询可能需要一些调整，但我将使用分解的子查询。 类似的东西

WITH unagg AS (
        SELECT DISTINCT tbl_a.name AS names, 
               first_value(foo) over (partition by name order by foo)
        FROM tbl_a )
SELECT LISTAGG(names, ';  ') WITHIN GROUP (ORDER BY names)
  FROM unagg
  JOIN tbl_b USING (foo)
 WHERE --condition

Answer 3

RLOG 的回答启发了我自己"解决"这个问题。这可能效率低下，但这对我的用例无关紧要。

我没有在查询第二个嵌套查询中进行过滤，而是获取所有内容，然后在第一个嵌套查询中过滤结果。

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

Answer 4

我认为您的答案可以缩短为这个。

  WITH all_names AS (
SELECT DISTINCT tbl_a.name
        FROM tbl_a
        INNER JOIN tbl_b ON tbl_a.foo = tbl_b.foo
        INNER JOIN tbl_c ON tbl_a.id = tbl_c.bar
  )
SELECT LISTAGG(name, ';  ') WITHIN GROUP (ORDER BY name)
  FROM all_names;