创建将层次结构存储为逗号分隔值的列(Create column that stores hierarchy as comma separated values)

我有一个表有两列UNIT ， M_UNIT ，如下所示：

+------+--------+ | UNIT | M_UNIT | +------+--------+ | 10 | 12 | | 15 | 19 | | 12 | 16 | | 13 | 15 | | 19 | 14 | | 14 | 11 | +------+--------+

我想创建一个H_TREE列，它在递归搜索UNIT和M_UNIT之后将层次结构存储为逗号分隔值，以便H_TREE以UNIT开头并以最后一个可能的M_UNIT结束，如下所示：

+------+--------+----------------+ | UNIT | M_UNIT | H_TREE | +------+--------+----------------+ | 10 | 12 | 10,12,16 | | 15 | 19 | 15,19,14,11 | | 12 | 16 | 12,16 | | 13 | 15 | 13,15,19,14,11 | | 19 | 14 | 19,14,11 | | 14 | 11 | 14,11 | +------+--------+----------------+

抱歉，如果我不够清楚，请告诉我是否有些令人困惑。谢谢。

I have a table which has two columns UNIT, M_UNIT as shown below:

+------+--------+ | UNIT | M_UNIT | +------+--------+ | 10 | 12 | | 15 | 19 | | 12 | 16 | | 13 | 15 | | 19 | 14 | | 14 | 11 | +------+--------+

I want to create a column H_TREE which stores hierarchy as comma separated values after recursively searching through UNIT & M_UNIT such that H_TREE starts with UNIT and ends with the last possible M_UNIT as shown below:

+------+--------+----------------+ | UNIT | M_UNIT | H_TREE | +------+--------+----------------+ | 10 | 12 | 10,12,16 | | 15 | 19 | 15,19,14,11 | | 12 | 16 | 12,16 | | 13 | 15 | 13,15,19,14,11 | | 19 | 14 | 19,14,11 | | 14 | 11 | 14,11 | +------+--------+----------------+

Apologies if I am not clear enough, let me know if something is confusing. Thanks.

最满意答案

这应该产生你想要的：

WITH data (unit, m_unit) AS ( SELECT 10, 12 FROM dual UNION ALL SELECT 15, 19 FROM dual UNION ALL SELECT 12, 16 FROM dual UNION ALL SELECT 13, 15 FROM dual UNION ALL SELECT 19, 14 FROM dual UNION ALL SELECT 14, 11 FROM dual) SELECT unit, m_unit, unit || ',' || listagg(root_unit, ',') WITHIN GROUP (ORDER BY depth) h_tree FROM ( SELECT id, unit, m_unit, LEVEL depth, CONNECT_BY_ROOT m_unit root_unit FROM (SELECT ROWNUM id, unit, m_unit FROM data) data CONNECT BY PRIOR unit = m_unit) GROUP BY id, unit, m_unit

如果表中的行是不同的，则不需要id列。

This should produce what you want:

WITH data (unit, m_unit) AS ( SELECT 10, 12 FROM dual UNION ALL SELECT 15, 19 FROM dual UNION ALL SELECT 12, 16 FROM dual UNION ALL SELECT 13, 15 FROM dual UNION ALL SELECT 19, 14 FROM dual UNION ALL SELECT 14, 11 FROM dual) SELECT unit, m_unit, unit || ',' || listagg(root_unit, ',') WITHIN GROUP (ORDER BY depth) h_tree FROM ( SELECT id, unit, m_unit, LEVEL depth, CONNECT_BY_ROOT m_unit root_unit FROM (SELECT ROWNUM id, unit, m_unit FROM data) data CONNECT BY PRIOR unit = m_unit) GROUP BY id, unit, m_unit

If the lines in your table are distinct the id column is not necessary.

更多推荐