逗号分隔的列表作为 Oracle 中的 select 语句的结果

2022-01-23 00:00:00 sql subquery oracle string-aggregation

我有一个名为person"的表.它包含人的 id 和它的父 id(只有一个父是可能的).作为查询的结果，我想要一个包含第一列的表 - 一个人 ID，第二列 - 它的子 ID 列表.究竟该怎么做?我已经阅读了 listagg 函数，但我不确定它是否适合我的目的.这个查询产生一个空的第二列:

I have a table named "person". It contains person's id and it's parent id (only one parent is possible). As a result of a query, I want a table with first column - a person id, and a second column - a list of it's children id's. How exactly to do this? I've read about listagg function, but I'm not sure if it is appropriate for my purpose. And this query produces an empty second column:

选择 t1.id，(从人员 t2 中选择 t2.id其中 t2.parent_id = t1.id) 来自人 t1其中 t1.status = '父';

select t1.id, (select t2.id from person t2 where t2.parent_id = t1.id) from person t1 where t1.status = 'parent';

推荐答案

SELECT parent_id,
       RTRIM(XMLAGG(XMLELEMENT(e,child_id || ',')).EXTRACT('//text()'),',') AS "Children"
  FROM parentChildTable
 WHERE parent_id = 0
 GROUP BY parent_id

或

SELECT parent_id,
       LISTAGG(child_id, ',') WITHIN GROUP (ORDER BY child_id) AS "Children"
  FROM parentChildTable
 WHERE parent_id = 0
 GROUP BY parent_id

相关文章