扭转递归查询中所有父母的订单

Question

这是我的查询：链接到sqlfiddle

with fruit_tree as 
(
   select [FRUIT_ID], [PARENT_FRUIT]
   ,CAST(FRUIT_NAME AS VARCHAR(max)) AS tree
   from FRUIT
   where [FRUIT_ID] = 'B03'
   union all
   select C.[FRUIT_ID], C.[PARENT_FRUIT]
   ,CAST(tree + ',' + CAST(c.FRUIT_NAME AS VARCHAR(max)) AS VARCHAR(max))
   from FRUIT c     
   join fruit_tree p on C.[FRUIT_ID] = P.[PARENT_FRUIT]
    AND C.[FRUIT_ID]<>C.[PARENT_FRUIT] 
)
select * from fruit_tree
where [PARENT_FRUIT] = '0'

上面的查询给出输出为：

FRUIT_ID    PARENT_FRUIT    tree
FR03        0               GOLDER BEAUTY,BANANA

，但我需要输出为：

FRUIT_ID    PARENT_FRUIT    tree
FR03        0               BANANA,GOLDER BEAUTY

我该如何实现？链接到sqlfiddle

Answer 1

如果您只想在输出的第三列中反转订单，则更改以下内容：

,CAST(tree + ',' + CAST(c.FRUIT_NAME AS VARCHAR(max)) AS VARCHAR(max))

to：

,CAST(CAST(c.FRUIT_NAME AS VARCHAR(max)) + ',' + tree AS VARCHAR(max))