我有一个用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
暂无答案!
目前还没有任何答案,快来回答吧!