如何从扁平表中提取层次结构？
如何从扁平表中提取层次结构？
提问于 2019-05-16 16:34:27
我想从oracle数据库的一个表中提取一个层次结构。表格如下所示：
+----+--------+----------------+---------------------+
| id | lvl1 | lvl2 | lvl3 |
+----+--------+----------------+---------------------+
| 1 | Oracle | Marketing unit | Internet |
+----+--------+----------------+---------------------+
| 2 | Oracle | Lawyers unit | Intellectual |
+----+--------+----------------+---------------------+
| 3 | Oracle | Finance unit | null |
+----+--------+----------------+---------------------+
| 4 | Oracle | Lawyers unit | Judicial department |
+----+--------+----------------+---------------------+
| 5 | Oracle | IT unit | Database |
+----+--------+----------------+---------------------+
| 6 | Oracle | Marketing unit | Television |
+----+--------+----------------+---------------------+
| 7 | Oracle | IT unit | ERP |
+----+--------+----------------+---------------------+这就是我想要得到的：
- Oracle
. - Marketing unit
. - Internet
. - Television
. - Lawyers unit
. - Intellectual
. - Judicial department
. - Finance unit
. - IT unit
. - Database
. - ERP我读到过Oracle分层查询，但我不知道如何使用我的表结构……最后，我必须让JSON显示在web页面上。为了方便起见，我在sqlfiddle上准备了一张桌子
我很感谢你的帮助，你有什么想法吗？
回答 1
Stack Overflow用户
回答已采纳
发布于 2019-05-16 17:30:02
一种方法是(使用UNPIVOT)：
WITH unpiv AS (SELECT lvl, par, cur, MIN(id) id FROM HIERARCHY_SAMPLE
unpivot ((par, cur) for lvl in ((lvl1 /*Something here - it doesn't really matter */,lvl1) as 1
,(lvl1,lvl2) as 2
,(lvl2,lvl3) as 3))
WHERE cur IS NOT NULL
GROUP BY lvl,par,cur)
SELECT LPAD('- ', LEVEL*2)||cur
FROM unpiv
START WITH lvl = 1
CONNECT BY lvl = PRIOR lvl + 1
AND par = PRIOR cur
ORDER siblings BY id页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56164226
复制相关文章
相似问题
腾讯云开发者
