Create table #country (AreaNam NVARCHAR(10),BelongTo Nvarchar(10),Msg varchar(100))

Let's insert a bunch of test data into this table:

INSERT INTO #country
SELECT 'China','China',null union all
SELECT 'Jiangsu','China',null union all
SELECT 'Nanjing','Jiangsu',null union all
SELECT 'Wuxi','Jiangsu',null union all
SELECT 'Xuzhou','Jiangsu',null union all
SELECT 'Yangzhou','Jiangsu',null union all
SELECT 'Suzhou','Jiangsu',null union all
SELECT 'Luhe','Nanjing',null union all
SELECT 'jiangning district','Nanjing',null union all
SELECT 'Pukou District','Nanjing',null union all
SELECT 'Xianlin District','Nanjing',null union all
SELECT 'jianye district','Nanjing',null union all
SELECT 'Baoying','Yangzhou',null union all
SELECT 'Signs','Yangzhou',null union all
SELECT 'Xiaoguanzhuang','Baoying',null union all
SELECT 'Fanshui','Baoying',null union all
SELECT 'Luduo','Baoying',null union all
SELECT 'Anyi','Baoying',null union all
SELECT 'Group Complete','Xiaoguanzhuang',null union all
SELECT 'House Bridge','Xiaoguanzhuang',null union all
SELECT 'Straight lower sulcus','Xiaoguanzhuang',null union all
SELECT 'Shandong','China',null union all
SELECT 'Jinan','Shandong',null union all
SELECT 'Qingdao','Shandong',null union all
SELECT 'Zibo','Shandong',null union all
SELECT 'Yantai','Shandong',null union all
SELECT 'Zhangdian','Zibo',null union all
SELECT 'Boshan','Zibo',null union all
SELECT 'Zichuan','Zibo',null union all
SELECT 'Longwang Mountain','Pukou District',null union all
SELECT 'High-tech Zone','Pukou District',null union all
SELECT 'Army Command Academy','Pukou District',null union all
SELECT 'nanjing university of information science and technology','Pukou District',null union all
SELECT 'Jinling College','Pukou District',null

At this point, the table structure is complete and should not be difficult to understand.

However, the following problems arise, and there is a requirement to find out all the place-names in Nanjing including those in Nanjing, because if there is a large amount of data, we can not determine how many levels of place-names a city has downwards. If there are too many levels, using circular fetching to achieve the query results is also a feasible solution. sql is as follows:

DECLARE @CITY NVARCHAR(MAX)='Nanjing'
Create table #TEMP (AreaNam NVARCHAR(10),BelongTo Nvarchar(10),Msg varchar(100))
Create table #tempAreaname (AreaNam NVARCHAR(10))
Create table #tempAreanametemp (AreaNam NVARCHAR(10))
INSERT INTO #tempAreaname 
SELECT @CITY
INSERT INTO #TEMP
SELECT * FROM #country WHERE AreaNam=@CITY
WHILE 1=1
BEGIN
	insert into #tempAreanametemp
	SELECT areanam  FROM #country WHERE belongto IN(select areanam from #tempAreaname)

	IF @@ROWCOUNT<>0
	BEGIN 
		INSERT INTO #TEMP
		SELECT * FROM #country WHERE belongto IN(select areanam from #tempAreaname)
		delete from #tempAreaname
		insert into #tempAreaname
		select * from #tempAreanametemp
		delete from #tempAreanametemp		
	END
	ELSE
	BEGIN	
		SELECT * FROM #TEMP
		DROP TABLE #TEMP
		DROP TABLE #tempAreaname
		DROP TABLE #tempAreanametemp
		RETURN
	END
END

AreaNam BelongTo Msg
Nanjing Jiangsu NULL
Liuhe District Nanjing NULL
Jiangning District Nanjing NULL
Pukou District Nanjing NULL
Xianlin District Nanjing NULL
Jianye District Nanjing NULL
NULL, Pukou District, Longwang Mountain
High-tech Zone Pukou Zone NULL
Army Command College Pukou District NULL
Pukou District NULL, Nanjing University of Information Engineering
Jinling University Pukou District NULL

That's exactly what we want, but I always feel that it's too complex and not very efficient to write. Of course, there must be a simpler way to write in a loop, which is not our focus today.Today's focus is on using CTE recursion to achieve the results we want, and SQL is as follows:

WITH CTE AS (
SELECT AreaNam,BelongTo,Msg FROM #country WHERE AreaNam='Nanjing'
UNION ALL
SELECT  A.AreaNam,A.BelongTo,A.Msg FROM #country A INNER JOIN CTE B ON A.BelongTo=B.AreaNam
)
SELECT * FROM CTE

The same is true for queries:

AreaNam BelongTo Msg
Nanjing Jiangsu NULL
Liuhe District Nanjing NULL
Jiangning District Nanjing NULL
Pukou District Nanjing NULL
Xianlin District Nanjing NULL
Jianye District Nanjing NULL
NULL, Pukou District, Longwang Mountain
High-tech Zone Pukou Zone NULL
Army Command College Pukou District NULL
Pukou District NULL, Nanjing University of Information Engineering
Jinling University Pukou District NULL