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一张留言表,不想另建留言回复表,初步设想成如上这样 id为自增列,fid对应为 最后的排列为 1 3 2 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!
排列是可以的,要用到递归,05以上用CTE可以解决,2000就用函数了。
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 goselect * 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 行受影响)
把fid列删除,alter table XX drop columns fid. 然后加fid列,alter table XX add columns fid. 然后再里面加数据咯。
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!
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
goselect *
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 行受影响)
然后加fid列,alter table XX add columns fid.
然后再里面加数据咯。