sql树形结果，查询所有子类

新建测试表

CREATE TABLE [dbo].[test](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[parentId] [bigint] NULL,
[name] [nvarchar](50) NULL,
[jb] [int] NULL,
)
GO

插入测试数据

INSERT INTO test ( Id, parentId, name, jb) values (1,0,'1',1);
INSERT INTO test ( Id, parentId, name, jb) values (2,0,'2',1);
INSERT INTO test ( Id, parentId, name, jb) values (3,1,'11',2);
INSERT INTO test ( Id, parentId, name, jb) values (4,1,'12',2);
INSERT INTO test ( Id, parentId, name, jb) values (5,2,'21',2);
INSERT INTO test ( Id, parentId, name, jb) values (6,2,'22',2);
INSERT INTO test ( Id, parentId, name, jb) values (7,3,'111',3);
INSERT INTO test ( Id, parentId, name, jb) values (8,3,'112',3);
INSERT INTO test ( Id, parentId, name, jb) values (9,5,'221',3);
INSERT INTO test ( Id, parentId, name, jb) values (10,0,'3',1);

 

树形结构，查询分类下所有子类

;with cte as
(
select * from test where id=1
union all
select a.* from test a join cte b on a.parentId=b.Id
)

select * from cte