ORACLE用逗号分隔的ID连接两个表 [英] ORACLE join two table with comma separated ids

问题描述

我有两个桌子

表1

ID     NAME
1      Person1
2      Person2
3      Person3

表2

ID     GROUP_ID
1      1
2      2,3

以上所有列中的ID均指相同的ID(例如-部门)

The IDs in all the columns above refer to the same ID (Example - a Department)

我的预期输出(通过同时连接两个表)

My Expected output (by joining both the tables)

GROUP_ID     NAME
1            Person1
2,3          Person2,Person3

有没有一个我可以实现这一目标的查询.

Is there a query with which I can achieve this.

推荐答案

可以完成.您不应该这样做，但是也许您没有改变世界的能力. (如果您有发言权，则应该对表设计进行规范化-在这种情况下，输入和输出都将失败第一个范式).

It can be done. You shouldn't do it, but perhaps you don't have the power to change the world. (If you have a say in it, you should normalize your table design - in your case, both the input and the output fail the first normal form).

为自己提供更多好的做法...此解决方案保证名称的列出顺序与ID的顺序相同.这不是最有效的方法，它不处理第一个表中未找到的列表中的ID(它只是丢弃它们而不是留下某种标记).

Answering more as good practice for myself... This solution guarantees that the names will be listed in the same order as the id's. It is not the most efficient, and it doesn't deal with id's in the list that are not found in the first table (it simply discards them instead of leaving a marker of some sort).

with
     table_1 ( id, name ) as (
       select 1, 'Person1' from dual union all
       select 2, 'Person2' from dual union all
       select 3, 'Person3' from dual
     ),
     table_2 ( id, group_id ) as (
       select 1, '1' from dual union all
       select 2, '2,3' from dual
     ),
     prep ( id, lvl, token ) as (
       select id, level, regexp_substr(group_id, '[^,]', 1, level)
       from   table_2
       connect by level <= regexp_count(group_id, ',') + 1
              and prior id = id
              and prior sys_guid() is not null
     )
select p.id, listagg(t1.name, ',') within group (order by p.lvl) as group_names
from   table_1 t1 inner join prep p on t1.id = p.token
group by p.id;

  ID GROUP_NAMES
---- --------------------
   1 Person1
   2 Person2,Person3

这篇关于ORACLE用逗号分隔的ID连接两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！