--建表语句
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

出处：https://www.cnblogs.com/armiwang/p/17592642.html