Oracle SQL从单个表创建不同级别的数据 [英] Oracle SQL creating different levels of data from a single table

问题描述

我正在尝试在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从单个表创建不同级别的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！