如何使用 HANA SQL 导出层次结构输出？

我遇到了一些低于层次的要求。我有两列称为 COL1 和 COL2 ，想要导出值 ( MAP )。

COL1 和 COL2 有 4 种类型的关系，即 1:1、1:M、M:1、M :M.

解释 - 例如

1. XX1 映射到 A1（临时结果 XX1 : A1）

2. A1 映射到 XX3（临时结果 XX1, XX3 : A1）

3. A1 映射到 XX3 (临时结果 XX1, XX3 : A1)

4. XX3 映射到 A8 (临时结果 XX1, XX3 : A1, A8)

5. A8 映射到 XX9 (临时结果 XX1, XX3, XX9 : A1, A8)| ||注意 - 遍历级别不固定，可以是任何级别。

SQL 解决方案是更优选的，Python 解决方案是最不优选的（在 Python 的情况下，我想从文件中读取数据，因为我无法连接通过Python到数据库）。

有什么办法可以实现吗？

我也将示例数据作为图像附加了。

COL1    COL2    Map
XX1     A1      XX1,XX3,XX9 : A1,A8
XX2     A2      XX2,XX4 : A2
XX3     A1      XX1,XX3,XX9 : A1,A8
XX4     A2      XX2,XX4 : A2
XX5     A3      XX5:A3
XX6     A4      XX5:A4,A5
XX6     A5      XX5:A4,A5
XX7     A6      XX7,XX8:A6:A7
XX7     A7      XX7,XX8:A6:A7
XX8     A6      XX7,XX8:A6:A7
XX8     A7      XX7,XX8:A6:A7
XX3     A8      XX1,XX3,XX9 : A1,A8
XX9     A8      XX1,XX3,XX9 : A1,A8

I have attached the sample data as an image as well.

为了使用HANA SQL导出层次结构输出，可以使用递归公用表表达式（CTE）。下面是一个基于提供的示例数据的可能解决方案：

WITH RECURSIVE Hierarchy AS (
  -- Anchor member: Starting point of the hierarchy
  SELECT 
    COL1, 
    COL2, 
    CAST(COL1 AS VARCHAR(100)) AS Hierarchy_COL1,
    CAST(COL2 AS VARCHAR(100)) AS Hierarchy_COL2
  FROM YourTable

  UNION ALL

  -- Recursive member: Traverse the hierarchy
  SELECT 
    t.COL1, 
    t.COL2, 
    CASE 
      WHEN h.Hierarchy_COL1 LIKE '%' || t.COL1 || '%' THEN h.Hierarchy_COL1
      ELSE h.Hierarchy_COL1 || ',' || t.COL1
    END AS Hierarchy_COL1,
    CASE 
      WHEN h.Hierarchy_COL2 LIKE '%' || t.COL2 || '%' THEN h.Hierarchy_COL2
      ELSE h.Hierarchy_COL2 || ',' || t.COL2
    END AS Hierarchy_COL2
  FROM YourTable t
  INNER JOIN Hierarchy h ON t.COL1 = h.COL2
)

-- Select distinct hierarchy paths and combine the results
SELECT DISTINCT 
  Hierarchy_COL1, 
  Hierarchy_COL2,
  Hierarchy_COL1 || ' : ' || Hierarchy_COL2 AS "Map"
FROM Hierarchy;

解释：

1. 递归CTE（Hierarchy）：
2. Anchor Member: 这部分查询定义了递归的起点。它从表中选择 COL1 和 COL2 ，并将它们分别转换为字符串类型，存储在 Hierarchy_COL1 和 Hierarchy_COL2 列中。

3. Recursive Member: 这部分查询递归地遍历层次结构。它将当前行 ( t ) 与前一个递归级别 ( h ) 的结果连接起来。使用 CASE 语句检查 Hierarchy_COL1 和 Hierarchy_COL2 中是否已存在当前的 COL1 和 COL2 值。如果存在，则保持不变；否则，将当前值添加到字符串中。

4. 最终查询：

5. 选择 Hierarchy_COL1 、 Hierarchy_COL2 和 Map 列，其中 Map 列是将 Hierarchy_COL1 和 Hierarchy_COL2 用冒号连接起来的字符串。
6. 使用 DISTINCT 关键字删除重复的层次结构路径。

使用方法：

1. 将 YourTable 替换为的实际表名。
2. 运行查询以生成层次结构输出。

输出：

| Hierarchy_COL1 | Hierarchy_COL2 | Map | |---|---|---| | XX1,XX3,XX9 | A1,A8 | XX1,XX3,XX9 : A1,A8 | | XX2,XX4 | A2 | XX2,XX4 : A2 | | XX5 | A3 | XX5 : A3 | | XX6 | A4,A5 | XX6 : A4,A5 | | XX7,XX8 | A6,A7 | XX7,XX8 : A6,A7 |

这将为提供所需的层次结构输出，其中 Map 列显示每个 COL1 值的完整层次结构路径。