Oracle SQL从单个表创建不同级别的数据

问题描述

我正在尝试在SELECT语句中创建新列，以从同一张表中挑选出顶层行.

I'm trying to create a new column in a SELECT statement that picks out top level lines from withing the same table.

样本数据:

ITEM_VALUE  DESCRIPTION         LEVEL_NO    ITEM_ABOVE
100         Ford                3           CAR
200         Honda Own           3           CAR
210         Honda 3rd Party     3           CAR
1000        Ford                4           100
2000        Honda T Own         4           200
801         Ford 1              4           1000
802         Ford 2              4           1000
803         Ford 3              4           1000
804         Ford 4              4           1000
805         Ford 5              4           1000
806         Ford 6              4           1000
807         Ford 7              4           1000
808         Ford 8              4           1000
814         Ford                4           1000
809         Honda               4           2000
2100        Honda T 3rd Party   4           210

期望的输出:

DESCRIPTION         ITEM_GROUP
Ford                Ford
Honda Own           Honda Own
Honda 3rd Party     Honda 3rd Party
Ford                Ford
Honda T Own         Honda Own
Ford 1              Ford
Ford 2              Ford
Ford 3              Ford
Ford 4              Ford
Ford 5              Ford
Ford 6              Ford
Ford 7              Ford
Ford 8              Ford
Ford                Ford
Honda               Honda Own
Honda T 3rd Party   Honda 3rd Party

推荐答案

您可以使用递归CTE:

You can use a Recursive CTE:

WITH CTE(ITEM_VALUE, ITEM_ABOVE, DESCRIPTION, ITEM_GROUP) AS
(
   SELECT ITEM_VALUE, ITEM_ABOVE, DESCRIPTION, DESCRIPTION AS ITEM_GROUP
   FROM mytable
   WHERE ITEM_ABOVE = 'CAR'

   UNION ALL

   SELECT t1.ITEM_VALUE, t1.ITEM_ABOVE, t1.DESCRIPTION, t2.ITEM_GROUP
   FROM mytable t1
   JOIN CTE t2 ON t1.ITEM_ABOVE = t2.ITEM_VALUE
)
SELECT ITEM_VALUE, ITEM_ABOVE, DESCRIPTION, ITEM_GROUP
FROM CTE

这篇关于Oracle SQL从单个表创建不同级别的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！