查询根目录下的所有记录！

/*
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
*/