常用SQL整理 - CTE递归

DECLARE @GEO_NODE TABLE
( [NODE_ID] [NVARCHAR](100) NOT NULL ,
  [LEVEL_NO] [INT] NULL ,
  [PNODE_ID] [NVARCHAR](100) NULL ,
  [NODE_DESC] [NVARCHAR](200) )

--LV.1 Country , LV.2 Company , LV.3 Region , LV.4 Area , lv.5 City Cluster , LV.6 City , LV.7 District

INSERT INTO @GEO_NODE VALUES('CHN001001001005012128','7','CHN001001001005012','CN002499')
INSERT INTO @GEO_NODE VALUES('CHN001002003013036282','7','CHN001002003013036','CN002501')
INSERT INTO @GEO_NODE VALUES('CHN001003006018049399','7','CHN001003006018049','CN003897')
INSERT INTO @GEO_NODE VALUES('CHN001001001005012','6','CHN001001001005','Changsha')
INSERT INTO @GEO_NODE VALUES('CHN001002003013036','6','CHN001002003013','FuzhouFJ')
INSERT INTO @GEO_NODE VALUES('CHN001003006018049','6','CHN001003006018','Hohhot')
INSERT INTO @GEO_NODE VALUES('CHN001001001005','5','CHN001001001','Changsha')
INSERT INTO @GEO_NODE VALUES('CHN001002003013','5','CHN001002003','Fuzhou')
INSERT INTO @GEO_NODE VALUES('CHN001003006018','5','CHN001003006','Huhuhaote')
INSERT INTO @GEO_NODE VALUES('CHN001001001','4','CHN001001','Central Cluster')
INSERT INTO @GEO_NODE VALUES('CHN001002003','4','CHN001002','Zhejiang & Fujian')
INSERT INTO @GEO_NODE VALUES('CHN001003006','4','CHN001003','TJ+HB+SX')
INSERT INTO @GEO_NODE VALUES('CHN001001','3','CHN001','CW')
INSERT INTO @GEO_NODE VALUES('CHN001002','3','CHN001','EAST')
INSERT INTO @GEO_NODE VALUES('CHN001003','3','CHN001','NORTH')
INSERT INTO @GEO_NODE VALUES('CHN001','2','CHN','Company China')
INSERT INTO @GEO_NODE VALUES('CHN','1','NULL','China')

;
WITH    cte
          AS ( SELECT   *
               FROM     @GEO_NODE
               WHERE    LEVEL_NO = 7
               UNION ALL
               SELECT   d.*
               FROM     cte c
               INNER JOIN @GEO_NODE d
               ON       c.PNODE_ID = d.NODE_ID )
     SELECT *
     FROM   cte