34,590
社区成员
发帖
与我相关
我的任务
分享
id name contents fid time
1 tom good! null 2010/01/01
2 jack very good! null 2010/01/02
3 frank thanks 1 2010/01/03
4 frank ok! 2 2010/01/04
create table tb(id int,[name] varchar(10),fid int,[time] datetime)
insert into tb
select 1,'tom',null,'2010/01/01' union all
select 2,'jack',null,'2010/01/02' union all
select 3,'frank',1,'2010/01/03' union all
select 4,'frank',2,'2010/01/04'
go
--2000
create function get_px(@id int)
returns varchar(1000)
as
begin
declare @ret varchar(1000)
declare @re table(id int,[level] int,ret varchar(1000))
declare @l int
set @l=0
insert @re select fid,@l,right(@id+1000000,4) from tb where id = @id
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.fid,@l,right(b.id+1000000,4)+b.ret from tb a,@re b
where a.id=b.id and b.level=@l-1 and a.id is not null
end
update @re set level=@l-level
select top 1 @ret = ret from @re order by [level]
return @ret
end
go
select *
from tb
order by dbo.get_px(id)
drop function get_px
drop table tb
/****************
id name fid time
----------- ---------- ----------- -----------------------
1 tom NULL 2010-01-01 00:00:00.000
3 frank 1 2010-01-03 00:00:00.000
2 jack NULL 2010-01-02 00:00:00.000
4 frank 2 2010-01-04 00:00:00.000
(4 行受影响)
查询结果
id name contents fid time
1 tom good! null 2010/01/01
3 frank thanks 1 2010/01/03
2 jack very good! null 2010/01/02
4 frank ok! 2 2010/01/04
界面数据绑定
tom发表于2010/01/01:good!
frank回复于2010/01/03:thanks
jack发表于2010/01/02:very good!
frank回复于2010/01/04:ok!