SQL 递归显示数据

create table CateGory(

ID varchar(30) primary key,--类别编号 Name varchar(30),--类别名称 UpName varchar(30)--上级类别 )

测试数据：

select * from category

insert into category values('001','中图分类','root') insert into category values('G','工业技术','001') insert into category values('H','语言、文字','001') insert into category values('H0','语言学','H')

insert into category values('H0-0','语言理论与方法论','H0')

insert into category values('H0-05','语言与其他学科的关系','H0-0') insert into category values('H0-06','语言学派、学说及其研究','H0') insert into category values('H1','汉语','H')

insert into category values('H0-09','语言发展与语言学史','H0') insert into category values('H2','中国少数民族语言','H')

insert into category values('H3','常用外国语','H') insert into category values('I','文学','H0') insert into category values('I0','文学理论','I')

insert into category values('I0-02','文学的哲学基础','I0') insert into category values('I01','文世美学','I0')

insert into category values('I02','文学理论的基础问题','I0')

insert into category values('I021','文学的民族化、大众化','I02') insert into category values('I022','文学与现实生活','I02') insert into category values('I023','文学的普及与提高','I02') insert into category values('I1','世界文学','I')

insert into category values('I2','中国文学','I') insert into category values('J','艺术','I')

insert into category values('K','历史地理','I')

递归查询语句：

WITH categoryInfo(id,name,upname) AS(

SELECT id,name,upname FROM category where upname='I' –-父节点条件 UNION ALL

SELECT a.* FROM category AS a inner join categoryInfo AS b on a.upname

= b.id )

SELECT * FROM categoryInfo