sql - 反转存储层次结构的逗号分隔列

Question

我有一个查询从下面的表中创建一个 H_TREE 列，

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

以下是我正在使用的查询，

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

上面的查询返回，

+------+--------+----------------+
| 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          |
+------+--------+----------------+

我想颠倒 H_TREE 列的顺序，使其如下所示，

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

为了实现这一目标，我需要对现有查询进行哪些更改？

根据评论:WITHIN GROUP (ORDER BY depth DESC) 给我以下结果，

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

Answer 1

尝试:

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,
    listagg(root_unit, ',') WITHIN GROUP (ORDER BY depth desc) || ',' || unit as 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