❶ 如何從資料庫中查詢出子類
例陸昌如：父類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 路徑(從父到子) 路徑(從子到父)