倒转存储层次结构的逗号分隔列

Question

倒转存储层次结构的逗号分隔列

3

我有一个查询，从下面的表中创建一个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         |
+------+--------+----------------+

- TheLastOne

1

在 WITHIN GROUP (ORDER BY depth DESC) 中添加 DESC。 - Mihai

这不会按照我在查询中提到的顺序返回。谢谢。 - TheLastOne

1

你能发布一下 "WITHIN GROUP (ORDER BY depth DESC)" 的结果吗？ - Haytem BrB

我已经在我的问题中添加了结果，谢谢 @haytem - TheLastOne

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Haytem BrB · Accepted Answer

尝试：

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