SQL SERVER中遞歸

--建表語句
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'MENU_INFO') DROP TABLE MENU_INFO;
CREATE TABLE MENU_INFO(
MENU_ID INT IDENTITY(1,1) NOT NULL,
MENU_NAME VARCHAR(100) NOT NULL,
PARETN_ID INT NULL,
CONSTRAINT MENU_INFO_PK1 PRIMARY KEY(MENU_ID),
CONSTRAINT MENU_INFO_AK1 UNIQUE(MENU_NAME)
);
--插入數據
INSERT INTO MENU_INFO (MENU_NAME,PARETN_ID) VALUES('一級菜單',NULL);
INSERT INTO MENU_INFO (MENU_NAME,PARETN_ID) VALUES('一級-二級菜單-1',1);
INSERT INTO MENU_INFO (MENU_NAME,PARETN_ID) VALUES('一級-二級菜單-2',1);
INSERT INTO MENU_INFO (MENU_NAME,PARETN_ID) VALUES('一級-二級菜單-3',1);
INSERT INTO MENU_INFO (MENU_NAME,PARETN_ID) VALUES('一級-二級菜單-1-三級菜單-1',2);
INSERT INTO MENU_INFO (MENU_NAME,PARETN_ID) VALUES('一級-二級菜單-1-三級菜單-2',2);
INSERT INTO MENU_INFO (MENU_NAME,PARETN_ID) VALUES('一級-二級菜單-1-三級菜單-3',2);
INSERT INTO MENU_INFO (MENU_NAME,PARETN_ID) VALUES('一級-二級菜單-2-三級菜單-1',3);
INSERT INTO MENU_INFO (MENU_NAME,PARETN_ID) VALUES('一級-二級菜單-2-三級菜單-2',3);
INSERT INTO MENU_INFO (MENU_NAME,PARETN_ID) VALUES('一級-二級菜單-2-三級菜單-3',3);
INSERT INTO MENU_INFO (MENU_NAME,PARETN_ID) VALUES('一級-二級菜單-3-三級菜單-1',4);
INSERT INTO MENU_INFO (MENU_NAME,PARETN_ID) VALUES('一級-二級菜單-3-三級菜單-2',4);
INSERT INTO MENU_INFO (MENU_NAME,PARETN_ID) VALUES('一級-二級菜單-3-三級菜單-3',4);
INSERT INTO MENU_INFO (MENU_NAME,PARETN_ID) VALUES('一級-二級菜單-3-三級菜單-1-四級菜單-1',11);
INSERT INTO MENU_INFO (MENU_NAME,PARETN_ID) VALUES('一級-二級菜單-3-三級菜單-1-四級菜單-2',11);
INSERT INTO MENU_INFO (MENU_NAME,PARETN_ID) VALUES('一級-二級菜單-3-三級菜單-1-四級菜單-3',11);
--查詢
WITH CON(MENU_ID,MENU_NAME,PARETN_ID,[LEVEL]) AS
(
SELECT MENU_ID,MENU_NAME,PARETN_ID,[LEVEL] = 1 FROM MENU_INFO WHERE MENU_ID = 4
UNION ALL
SELECT A.MENU_ID,A.MENU_NAME,A.PARETN_ID, [LEVEL] = [LEVEL]+ 1 FROM MENU_INFO A JOIN CON ON A.PARETN_ID = CON.MENU_ID
)
SELECT MENU_ID,MENU_NAME,PARETN_ID,[LEVEL] FROM CON