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)