❶ 如何从数据库中查询出子类
例陆昌如：父类ID为FATHER_ID、子类ID为SUB_ID。语句如下
SELECT SUB_ID FROM TABLE_NAME WHERE FATHER_ID='';
例如宴悉亩：两张表，父类晌森表为F_TAB、子类表为S_TAB。那么子类表中必定有父类ID的字段，关联查询就好。语句如下
SELECT F.FATHER_NAME,S.SUB_NAME FROM S_TAB S,F_TAB F WHERE S.FATHER_ID=F.FATHER_ID
❷ sql语句 查询 类别下所有子类
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
❸ SQL 如果查询所有一级类和对应的二级子类
select * from News left join Newclass on News.ClassID=NewClass.ClassID where Newclass.classParentID=News.ClassID 没肆蚂怎么仔细看 我理解的是 新闻表是主悄雹蔽表 查询的是 所有newclass表里所有 classparentId等于ClassId的数据启州
❹ sql 父类下的子类查询的方法，包含父类的信息
-查询各节点的父路径函数(从父到子)
create function f_pid1(@id varchar(3)) returns varchar(100)
as
begin
declare @re_str as varchar(100)
set @re_str = '纤困滚'尺棚
select @re_str = name from tb where id = @id
while exists (select 1 from tb where id = @id and pid is not null)
begin
select @id = b.id , @re_str = b.name + ','毁余 + @re_str from tb a , tb b where a.id = @id and a.pid = b.id
end
return @re_str
end
go
--查询各节点的父路径函数(从子到父)
create function f_pid2(@id varchar(3)) returns varchar(100)
as
begin
declare @re_str as varchar(100)
set @re_str = ''
select @re_str = name from tb where id = @id
while exists (select 1 from tb where id = @id and pid is not null)
begin
select @id = b.id , @re_str = @re_str + ',' + b.name from tb a , tb b where a.id = @id and a.pid = b.id
end
return @re_str
end
go
select * ,
dbo.f_pid1(id) [路径(从父到子)] ,
dbo.f_pid2(id) [路径(从子到父)]
from tb order by id
drop function f_pid1 , f_pid2
drop table tb
/*
id pid name 路径(从父到子) 路径(从子到父)