Question

sql语句 查询 类别下所有子类

如下
（ID）类别ID （Name） 类型名称 (FID)父类ID
1 一级1 0
2 一级2 0
3 二级1 2
4 二级2 2
5 三级1 4
6 四级1 5
7 二级1 1
求一条sql语句查询出，ID为2,3,4,5,6这几条数据

Answer 1

create table tb
(
ID int,
name varchar(20),
FID int
)

insert into tb values( 1,'一级1',0)
insert into tb values( 2,'一级2',0)
insert into tb values( 3,'二级1',2)
insert into tb values( 4,'二级2',2)
insert into tb values( 5,'三级1',4)
insert into tb values( 6,'四级1',5)
insert into tb values( 7,'二级1',1)

with tmp(id,name,fid) as
(
select id,name,fid from tb where id in (2)
union all
select tb.id,tb.name,tb.fid from tb inner join tmp on tb.fid = tmp.id
)
select distinct * from tmp order by id,fid

2 一级2 0
3 二级1 2
4 二级2 2
5 三级1 4
6 四级1 5

Answer 2

--假如表名是 tablea
select *
from tablea b
where ID between 2 and 6

union all

select *
from tablea a
where exists
(
select *
from tablea b
where b.ID between 2 and 6
and a.ID=b.FID

)
--第二办法是使用递归
with
temp
as
(
select *
from tablea
where ID between 2 and 6

union all

select b.*
from temp
inner join tablea b on b.ID=temp.ID
)

select *
from temp

Answer 3

select * from TABLE where id in (2,3,4,5,6);

Answer 4

select * from table where id in (2,3,4,5,6)