SQL父子查询 - 关系在两个表中定义(SQL Parent Child query - relation is defined in two tables)

 我知道这里有类似的问题，但我找不到他的特殊情况，所以我想问你，我希望你会告诉我如何做到这一点更好。 
 
 我有这个SQL Fiddle 。 
 我有两张桌子： 
 
CATEGORIES (ID, 
             NAME, 
             PARENT_ID, 
             SORT_ORDER)
 
 和 
 
CATEGORY_ITEMS (ID,
                 NAME, 
                 CATEGORY_ID, 
                 SORT_ORDER)
 
 这两个表在某种程度上是我的真实数据库模式的简历（我没有可能改变它）。 在我的真实数据库模式中，这些类别可以定义基于特定日期时间值有效的集合。 我想知道是否可以通过一个查询返回如下所示的结果。 
 
|           NAME    | 
|-------------------|
|Category 1         |   
|Item 1 Category 1  |  
|Item 2 Category 1  |   
|Category 1.1       | 
|Item 1 Category 1.1|    
|Item 2 Category 1.1|      
|Category 1.2       |      
| Category 1.2.1    |       
|     Category 2    |   
|Item 1 Category 2  |   
|Item 2 Category 2  |    
|   Category 2.1    |       
|   Category 2.2    |        
|     Category 3    |    
|   Category 3.1    |        
|   Category 3.2    | 
 
 在SQL Fiddle中，你可以看到我的工作到现在为止。 我认为我可以通过将树结构从CATEGORIES转换为游标来获得我想要的东西，并且对于每个记录我应该从CATEGORY_ITEMS获取它的记录，但我想知道是否有可能在没有游标的情况下执行它。 我承认我不擅长SQL，但我想要做得更好，这就是我问这个问题的原因。 

I know that there are similar questions here but I couldn't find my particular situation in them, so I want to ask you and I hope that you will advise me on how it's better to do it.
 
I have this SQL Fiddle.
 I have two tables: 
 
CATEGORIES (ID, 
             NAME, 
             PARENT_ID, 
             SORT_ORDER)
 
And 
 
CATEGORY_ITEMS (ID,
                 NAME, 
                 CATEGORY_ID, 
                 SORT_ORDER)
 
These two tables are somehow the resume of my real database schema (I don't have the possibility to change it). In my real database schema, these categories can define sets that are valid based on certain datetime values. I would like to know if it is possible to return results like below with one query. 
 
|           NAME    | 
|-------------------|
|Category 1         |   
|Item 1 Category 1  |  
|Item 2 Category 1  |   
|Category 1.1       | 
|Item 1 Category 1.1|    
|Item 2 Category 1.1|      
|Category 1.2       |      
| Category 1.2.1    |       
|     Category 2    |   
|Item 1 Category 2  |   
|Item 2 Category 2  |    
|   Category 2.1    |       
|   Category 2.2    |        
|     Category 3    |    
|   Category 3.1    |        
|   Category 3.2    | 
 
In the SQL Fiddle you can see my work until now. I think that I can obtain what I want by getting the tree structure from CATEGORIES into a cursor and for each record I should get it's records from CATEGORY_ITEMS, but I was wondering if there is a possibility to do it without a cursor. I admit that I am not to good at SQL but I am trying to be better so that's why I am asking this question.

原文：https://stackoverflow.com/questions/30421237