递归查询 sqlserver

1.表 T_SYS_DISTRICT

 

 

2.语句

WITH temp (OID,NAME,PARENT_OID) AS
(
SELECT D.OID,D.NAME,D.PARENT_OID FROM T_SYS_DISTRICT D WHERE D.IS_DELETE = 'N' AND OID = '2'
UNION ALL
SELECT S.OID,S.NAME,S.PARENT_OID FROM T_SYS_DISTRICT S,temp T WHERE S.IS_DELETE = 'N' AND S.PARENT_OID = T.OID
) SELECT OID,NAME,PARENT_OID FROM temp;

 

 

4.结果