hive中的java递归cte

f45qwnt8  于 2021-06-26  发布在  Hive
关注(0)|答案(0)|浏览(454)

我有一个用SQLServer编写的递归cte,我需要将其迁移到hive。由于hive不支持cte中的递归,我被困在这里。我被建议为此编写一个javaudf,但我并不擅长java。如果有人能帮上忙,我就太好了。
代码(sql server):

WITH CTE_CATEGORY_TREE AS 
            (
                                            SELECT CT.seq
                                                            , CT.[name]
                                                            , CT.pseq
                                                            , convert(varchar(255), CT.seq) AS [sort]
                                                            , convert(nvarchar(255), CT.[name]) AS [depth_fullname]
                                            FROM [dbo].[table1] AS CT WITH(NOLOCK)
                                            WHERE CT.pseq = 0 
                            UNION ALL 
                                            SELECT PC.seq
                                            , PC.[name]
                                            , PC.pseq
                                            , convert(varchar(255), convert(nvarchar,CT2.sort) + N' > ' + convert(varchar(255), PC.seq)) AS [sort]
                                            , convert(nvarchar(255), convert(nvarchar,CT2.depth_fullname) + N' > ' + convert(nvarchar(255), PC.[name])) AS [depth_fullname] 
                                            FROM [dbo].[table1] AS PC WITH(NOLOCK)
                                            JOIN CTE_CATEGORY_TREE AS CT2 ON PC.pseq = CT2.seq
            )

            SELECT          seq
                            , [name]
                            , pseq
                            , sort
                            , depth_fullname 
            FROM CTE_CATEGORY_TREE

暂无答案!

目前还没有任何答案,快来回答吧!

相关问题