1：递归查询子信息，并查询出多少条子信息
WITH CTE AS (SELECT ORG_ID, PARENT_ORG_ID, ORG_NAME, SORT FROM SYS_ORG WHERE 1=1 AND ORG_ID='F80FE6B0-4A83-46F5-B78C-A17CC49AD172' UNION ALL SELECT C.ORG_ID, C.PARENT_ORG_ID, C.ORG_NAME, C.SORT FROM SYS_ORG C INNER JOIN CTE P ON P.ORG_ID = C.PARENT_ORG_ID) SELECT ORG_ID, PARENT_ORG_ID, ORG_NAME, SORT, ( SELECT CAST(COUNT(*) AS DECIMAL) FROM CTE C1 WHERE C1.PARENT_ORG_ID = C2.ORG_ID ) CHILD_COUNT FROM CTE C2 ORDER BY PARENT_ORG_ID, SORT
2：递归查询子信息
WITH CTE AS
(SELECT ORG_ID, PARENT_ORG_ID, ORG_NAME, SORT
FROM SYS_ORG
WHERE 1=1 AND ORG_ID='F80FE6B0-4A83-46F5-B78C-A17CC49AD172'
UNION ALL
SELECT C.ORG_ID, C.PARENT_ORG_ID, C.ORG_NAME, C.SORT
FROM SYS_ORG C
INNER JOIN CTE P
ON P.ORG_ID = C.PARENT_ORG_ID)
SELECT ORG_ID, PARENT_ORG_ID, ORG_NAME, SORT FROM CTE ORDER BY PARENT_ORG_ID, SORT
(SELECT ORG_ID, PARENT_ORG_ID, ORG_NAME, SORT
FROM SYS_ORG
WHERE 1=1 AND ORG_ID='F80FE6B0-4A83-46F5-B78C-A17CC49AD172'
UNION ALL
SELECT C.ORG_ID, C.PARENT_ORG_ID, C.ORG_NAME, C.SORT
FROM SYS_ORG C
INNER JOIN CTE P
ON P.ORG_ID = C.PARENT_ORG_ID)
SELECT ORG_ID, PARENT_ORG_ID, ORG_NAME, SORT FROM CTE ORDER BY PARENT_ORG_ID, SORT