WITH CTE1 AS (SELECT *,DENSE_RANK() OVER (ORDER BY LEN(INNERCODE)) AS RN FROM TABLE),CTE2 AS (SELECT * FROM CTE1 A WHERE NOT EXISTS (SELECT 1 FROM CTE1 WHERE RN>A.RN) AND INNERCODE=最底层INNERCODE UNION ALL SELECT B.* FROM CTE1 B JOIN CTE2 C ON B.RN=C.RN-1 AND C.INNERCODE LIKE '%'+B.INNERCODE+'%')SELECT * FROM CTE2
WITH CTE1
AS
(SELECT *,DENSE_RANK() OVER (ORDER BY LEN(INNERCODE)) AS RN
FROM TABLE),CTE2
AS
(SELECT * FROM CTE1 A WHERE NOT EXISTS (SELECT 1 FROM CTE1 WHERE RN>A.RN) AND INNERCODE=最底层INNERCODE
UNION ALL
SELECT B.* FROM CTE1 B
JOIN CTE2 C ON B.RN=C.RN-1 AND C.INNERCODE LIKE '%'+B.INNERCODE+'%')SELECT * FROM CTE2