Oracle中的select语句导致逗号分隔的列表 [英] comma-separated list as a result of select statement in Oracle

问题描述

我有一个名为"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 其中人t1的t2.parent_id = t1.id) 其中t1.status ='parent';

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

这篇关于Oracle中的select语句导致逗号分隔的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！