sys中的sys_connect_by_path等价物 [英] sys_connect_by_path equivalent in sql

问题描述

我在ORACLE查询中使用了sys_connect_by_path。现在我想将它转换为sql server。

I have used sys_connect_by_path in ORACLE query.Now I want to convert it to sql server.

(select type_id,id,cast(name,'\') obs_name  from  prj_obs_units 
start with  id in ( select id  from prj_obs_units  where parent_id is  null )
connect  by  prior id=parent_id) 

你能告诉我如何将它转换成sql server查询，在此先感谢。

can you tell me how to convert this into sql server query , Thanks in Advance.

推荐答案

您将不得不使用递归公用表表达式（CTE）。我没有你的架构，但我相信你的Oracle查询会产生相同的结果：

You will have to use a recursive Common Table Expression (CTE). I don't have your schema, but I believe from your Oracle query that this will yield the same result:

WITH MyCTE(type_id, id, obs_name)AS
(SELECT type_id, id, CAST([name] AS varchar(1024)) 
FROM prj_obs_units 
WHERE id parent_id IS NULL
UNION ALL
SELECT c.type_id, c.id, CAST(p.obs_name + '\' + c.[name] AS varchar(1024))
FROM prj_obs_units AS c INNER JOIN MyCTE AS p ON c.parent_id = p.id)

SELECT type_id, id, obs_name
FROM MyCTE

这篇关于sys中的sys_connect_by_path等价物的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！