使用 Oracle 中的函数与 3 个相关表将多行连接到一列中

Question

我知道关于同一问题有多个问题，但我找不到可以解决我的问题的问题。我也知道这篇文章，这正是我用于代码的内容以下。我只是遇到了一个小问题...

table_a:

a_id    a_name
1       aaa
2       bbb
3       ccc

table_b:

b_id    b_name
1       ddd
2       eee
3       fff

table_c

a_id    b_id
1       2
1       3
2       1
3       1
3       2
3       3

我有上面的表格，我需要一个 SELECT 语句来完成这个：

a_name  list_of_b_name
aaa     eee,fff
bbb     ddd
ccc     ddd,eee,fff

也许我可以使用一些Oracle函数来简化过程，比如WM_CONCAT（还没有尝试过），但我需要使用一个FUNCTION，这是我在查看上面链接的文章后的尝试：

CREATE OR REPLACE
FUNCTION f_test(id IN table_c.a_id % TYPE) RETURN VARCHAR2 IS
  l_text  VARCHAR2(32767) := NULL;
BEGIN
  FOR cur_rec IN (SELECT b_id FROM table_c WHERE a_id = id) LOOP
    l_text := l_text || ',' || cur_rec.b_id;
  END LOOP;
  RETURN LTRIM(l_text, ',');
END;

然后选择像这样：

SELECT a_id, f_test(a_id)
FROM table_c
GROUP BY a_id;

正如上面的代码所期望的（不是我想要的，这就是我得到的）：

a_id    list_of_b_id
1       2,3
2       1
3       1,2,3

我已经用 SELECT 和 FUNCTION 代码尝试了很多东西，但我似乎无法做我需要的事情......

Answer 1

您需要将您的函数更改为类似于：

CREATE OR REPLACE FUNCTION f_test(IN_ID IN TABLE_C.a_id%TYPE) 
RETURN VARCHAR2 
IS

  l_text  VARCHAR2(32767) := NULL;

BEGIN

  FOR cur_rec IN (SELECT b.b_name 
                    FROM TABLE_B b
                    JOIN TABLE_C c ON c.b_id = b.b_id
                   WHERE c.a_id = IN_ID) LOOP
    l_text := l_text || ',' || cur_rec.b_id;
  END LOOP;

  RETURN LTRIM(l_text, ',');

END;

然后，您可以使用：

SELECT a.a_name, 
       f_test(a.a_id)
  FROM TABLE_A a

Answer 2

更改 SQL 查询以获取 bname 列表而不是 b_ids

从

SELECT b_id FROM table_c WHERE a_id = id

到

SELECT b_name 
  FROM table_b b, table_c c 
   WHERE b.b_id = c.c_id
     and b.a_id = id

您不应该从表 b 获取 ID，而应该获取名称。

Answer 3

首先，正如 @OMGPonies 指出的，您需要重写该函数，以便它从 TABLE_B 检索所需的数据。

SQL> create or replace function f_test
  2      (p_id in table_c.a_id%type)
  3      return varchar2
  4  is
  5      l_text  varchar2(32767) := null;
  6  begin
  7      for cur_rec in (select b_name
  8                      from table_c c
  9                              join table_b b
 10                                  on (b.b_id = c.b_id)
 11                      where c.a_id = p_id)
 12      loop
 13          l_text := l_text || ',' || cur_rec.b_name;
 14      end loop;
 15      return ltrim(l_text, ',');
 16  end;
 17  /

Function created.

SQL>

同样，您需要在查询中加入 TABLE_A：

SQL> select a.a_name
  2          , f_test(c.a_id)
  3  from table_c c
  4          join table_a a
  5              on (a.a_id = c.a_id)
  6  group by a.a_name
  7  /
        , f_test(c.a_id)
                 *
ERROR at line 2:
ORA-00979: not a GROUP BY expression


SQL>

哦，这真是一个惊喜。关键是，F_TEST 不是聚合函数，因此除非我们也包含该函数，否则 GROUP BY 在这里不起作用：

SQL> select a.a_name
  2          , f_test(c.a_id) 
  3  from table_c c
  4          join table_a a
  5              on (a.a_id = c.a_id)
  6  group by a.a_name, f_test(c.a_id)
  7  /

A_N F_TEST(C.A_ID)
--- ---------------------------------------------
bbb ddd
ccc ddd,eee,fff
aaa eee,fff

SQL>

顺便说一句，如果没有 GROUP BY 子句，我们将得到六行（TABLE_C 中的每一行各一行）。这是我们不想要的。

该解决方案的问题在于性能：该函数针对 TABLE_C 中的每一行执行一次。如果任一表很大，TABLE_B 上的循环会变得有点昂贵。聚合解决方案更有效：

SQL> select a.a_name
  2          , wm_concat(b.b_name) 
  3  from table_c c
  4          join table_b b
  5              on (b.b_id = c.b_id)
  6          join table_a a
  7              on (a.a_id = c.a_id)
  8  group by a.a_name
  9  /

A_N WM_CONCAT(B.B_NAME)
--- ---------------------------------------------
aaa fff,eee
bbb ddd
ccc fff,eee,ddd

SQL>