在Oracle有start with connect by的查询语句,可以实现层次查询,这在sqlserver中是没有的(笔者最新版本为2008R2,未知新版中是否有),不得不说,Oracle在查询语句功能上比sqlserver丰富多了!
在Oracle,是这么来的:
SELECT
level,
chil_item_cod,
SYS_CONNECT_BY_PATH(chil_item_cod,‘/’) AS PATH
FROM
m_structure
WHERE
del_F=0 start with del_F=0 AND
pare_item_Cod='MN2_TESTXX' connect by prior pare_item_cod=chil_item_cod
在sqlserver2008R2中,可以试着这么来:
with subqry(id,name,parent_id) as(
select id,name,parent_id from pro_type_manage where id in ('5799936','5799946') -- start with
union all
select test1.id,test1.name,test1.parent_id from pro_type_manage as test1,subqry
where test1.parent_id = subqry.id --connect by
)
select* from subqry;
以上语句没有关联性,大家看例子变通实现就可以。
以上是知道不整理的《在sqlserver2008以上版本中实现Oracle的start with connect by相似功能》.希望对您有帮助。
via1:https://blog.csdn.net/blue_sky_blue_heart/article/details/1627390
via2:https://blog.csdn.net/iteye_18075/article/details/82641277