--创建用户定义函数(不能真正实现无级排序,因为受varchar数据类型支持最大长度限制)
create function f_getOrder(@SectionID int)
returns varchar(8000)
as
begin
declare @Order varchar(8000),@P@SectionID int
select
@Order = right('00000'+rtrim(@SectionID),6),
@PSectionID = ParentSectionID
from
Sections
where
SectionID=@SectionID
while @PSectionID<>0
begin
set @SectionID=@PSectionID
select
@Order = right('00000'+rtrim(@SectionID),6)+@Order,
@PSectionID = ParentSectionID
from
Sections
where
SectionID=@SectionID
end
return @Order
end
go--调用
select
GroupID,SectionID,SectionName
from
Sections
order by
GroupID,dbo.f_getOrder(SectionID)
create function f_getOrder(@SectionID int)
returns varchar(8000)
as
begin
declare @Order varchar(8000),@P@SectionID int
select
@Order = right('00000'+rtrim(@SectionID),6),
@PSectionID = ParentSectionID
from
Sections
where
SectionID=@SectionID
while @PSectionID<>0
begin
set @SectionID=@PSectionID
select
@Order = right('00000'+rtrim(@SectionID),6)+@Order,
@PSectionID = ParentSectionID
from
Sections
where
SectionID=@SectionID
end
return @Order
end
go--调用
select
GroupID,SectionID,SectionName
from
Sections
order by
GroupID,dbo.f_getOrder(SectionID)
---------
@Order前面空位补0