Posted on 2012-10-23 15:04
yuhaibo736 閱讀(724)
評論(0) 編輯 收藏
WITH CTE AS
(
SELECT OBJ_VW_ID,NODE_CODE ,P_NODE_CODE,NODE_TYP ,NODE_CAP,OBJ_VW_ENG_NAME,SN,ICON_TYP,rn=cast(NODE_CODE as varchar(max)),level = 1 FROM DSK_OBJ_VW_DISP WHERE NODE_CODE in (select NODE_CODE from DSK_OBJ_VW_DISP where P_NODE_CODE='2')
UNION ALL
SELECT T.OBJ_VW_ID,T.NODE_CODE,T.P_NODE_CODE,T.NODE_TYP,T.NODE_CAP,T.OBJ_VW_ENG_NAME,T.SN,T.ICON_TYP,rn=rn+cast(T.NODE_CODE AS VARCHAR(MAX)), level=level+1 FROM DSK_OBJ_VW_DISP T,CTE WHERE CTE.NODE_CODE=T.P_NODE_CODE
)
SELECT OBJ_VW_ID,NODE_CODE ,P_NODE_CODE,NODE_TYP,NODE_CAP,OBJ_VW_ENG_NAME,SN,ICON_TYP,rn,level FROM CTE ORDER BY rn