SQL-具有父子关系的排序表 [英] SQL - sorting table with parent child relation

问题描述

我们有一个带有父子关系的表，希望对它进行排序.排序标准是这样，以便在遍历结果时，与父ID匹配的行应该已经存在:

We have a table with a parent-child relation and would like to get it sorted. The sorting criteria is so that when iterating though the result the row matching a parent ID should already be there :

ID   PARENT_ID 
EF01 EF02         // This is wrong as the row EF02 is after and will fail.
EF02    
BB   AA           // here BB < AA
AA   EF01 

问题在于两个键都是字符串，因此按ID或PARENT_ID排序无法解决问题.

The problem is that both key are string, therefore sorting by ID or PARENT_ID is not going to fix the problem.

推荐答案

对于Oracle，使用

For Oracle, using hierarchical queries:

 select id, parent_id, level from the_table
 start with parent_id is null
 connect by prior id = parent_id;

这篇关于SQL-具有父子关系的排序表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！