/*
SupTypeID代表 上级目录 0表示一级目录
SupTypeID=1表示为ID=1的子目录,依此类推,ID=8 是ID=6 的子目录
读取ID=1时把他所有的产品读出来
*/
if exists (select 1 from sysobjects where id=object_id('tt'))
drop table tt
create table tt(ID int,TypeName varchar(50),SupTypeID int)
insert into tt
select '1', 'Agriculture', 0
union all select '2', ' Apparel & Fashion', 0
union all select '3', ' Automobile',0
union all select '4', ' Business Services', 0
union all select '5', ' Agriculture & By-product Agents', 1
union all select '6', 'Agriculture Product Stocks', 1
union all select '7', ' Agriculture Products Processing', 1
union all select '8', ' Other', 6
union all select '9', ' Animal Extract', 6
union all select '10', 'Animal Fodders', 7
select * from tt
/*
ID TypeName SupTypeID
---------------------------------------------------------------------------------------------
1 Agriculture 0
2 Apparel & Fashion 0
3 Automobile 0
4 Business Services 0
5 Agriculture & By-product Agents 1
6 Agriculture Product Stocks 1
7 Agriculture Products Processing 1
8 Other 6
9 Animal Extract 6
10 Animal Fodders 7
*/
drop function F_GetChlidren
go
create function F_GetChlidren(@id int)
returns @tree table (ID int,SupTypeID int)
as
begin
insert @tree select ID,SupTypeID from tt where ID=@id
while @@rowcount>0
insert @tree
select a.ID,a.SupTypeID from tt a inner join @tree b on a.SupTypeID=b.ID and a.ID not in (select id from @tree )
return
end
go
select a.* from tt a, dbo.F_GetChlidren(1) b where a.ID=b.ID
/*
ID TypeName SupTypeID
---------------------------------------------------------------------------------------------
1 Agriculture 0
5 Agriculture & By-product Agents 1
6 Agriculture Product Stocks 1
7 Agriculture Products Processing 1
8 Other 6
9 Animal Extract 6
10 Animal Fodders 7
*/