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

本文介绍了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