-- 示例数据
CREATE TABLE tb(fid int, fparentid int)
INSERT tb SELECT 1, 0
UNION ALL SELECT 2, 0
UNION ALL SELECT 3, 1
UNION ALL SELECT 4, 2
UNION ALL SELECT 5, 3
GO
-- 生成 path 的函数
CREATE FUNCTION dbo.f_path(
@fid int
) RETURNS varchar(8000)
AS
BEGIN
DECLARE @re varchar(8000)
SELECT @re = RIGHT(10000 + fid, 4), @fid = fparentid
FROM tb
WHERE fid = @fid
WHILE @@ROWCOUNT > 0
SELECT @re = RIGHT(10000 + fid, 4) + @re, @fid = fparentid
FROM tb
WHERE fid = @fid
RETURN(@re)
END
GO
-- 调用函数实现排序
SELECT * FROM tb
ORDER BY dbo.f_path(fid)
GO
if object_id('testtable') is not null drop table testtable
go
create table testtable
(
fid int,
fparentid int
)
insert into testtable
select 1,0 union all
select 2,0 union all
select 3,1 union all
select 4,2 union all
select 5,3
go
if object_id('f_test') is not null drop function f_test
go
create function f_test(@a int)
returns int
as
begin
declare @r int
set @r=@a
while not exists (select 1 from testtable where fparentid=0 and fid=@r)
and exists (select 1 from testtable where fid=@r)
select @r=fparentid from testtable where fid=@r
return @r
end
go
select *
from testtable
order by dbo.f_test(fid),fparentid
if object_id('testtable') is not null drop table testtable
go
create table testtable
(
fid int,
fparentid int
)
insert into testtable
select 1,0 union all
select 2,0 union all
select 3,1 union all
select 4,2 union all
select 5,3
go
if object_id('f_test') is not null drop function f_test
go
create function f_test(@a int)
returns int
as
begin
declare @r int
set @r=@a
while not exists (select 1 from testtable where fparentid=0 and fid=@r)
and exists (select 1 from testtable where fid=@r)
select @r=fparentid from testtable where fid=@r
return @r
end
go
select *
from testtable
order by dbo.f_test(fid)