刚看了个人想用sql实现父联表的树形实现，看了下，自己用sql2000的语法写了个。。

CREATE TABLE t1

(

model INT,

ModelName    VARCHAR(50),

ParentId    INT

)

INSERT INTO t1 VALUES(100,'商品管理',0)

INSERT INTO t1 VALUES(101,'订单管理',0)

INSERT INTO t1 VALUES(107,'商品管理',100)

INSERT INTO t1 VALUES(108,'明细管理',100)

INSERT INTO t1 VALUES(109,'三级菜单',107)

 

 

alter function getDep(@model int )

returns varchar(200)

as

begin

declare @returns varchar(200)

Set @returns='┝'

declare @temp int

set @temp= (select ParentId from t1 where model=@model)

while(@temp<>0)

begin

set @returns='  '+@returns

set @temp=(select ParentId from t1 where model=@temp)

end

return @returns

end

 

alter function getbb(@model int )

returns varchar(200)

as

begin

declare @returns varchar(200)

set @returns=@model

declare @temp varchar(200)

set @temp= (select ParentId from t1 where model=@model)

while(@temp<>0)

begin

set @returns=Convert(varchar(200),@temp)+Convert(varchar(200),@model)

set @model=@returns

set @temp=(select ParentId from t1 where model=@temp)

end

return @returns

end

 

 

select *,dbo.getDep(model)+ModelName from t1 order by dbo.getbb(model)