select 0 ContentId, identity(int,1,1) id into #t declare @orderid int declare @cid int set @orderid = 1 while exists (select * from ContentTable where AllOrderId = 0) begin select top 1 @cid = ContentId from #t order by id desc if exists (select ContentId from ContentTable where ContentSort = @cid and AllOrderId = 0) begin select top 1 @cid = ContentId from ContentTable where ContentSort = @cid and AllOrderId = 0 order by orderid insert #t (contentid) values (@cid) update ContentTable set Allorderid = @orderid where contentid = @cid set @orderid = @orderid + 1 end else begin delete #t where contentid = @cid end end declare @s varchar(1000) set @s = '' select @s = @s + rtrim(cast(contentid as char))+ '-' from ContentTable order by AllOrderId select left(@s, len(@s)-1) drop table #t1-22-25-23-26-21-24-30-2
angle1219(阿光),修改一下函数,游标按你的OrderId排序就可以了.
to:tj_dns(愉快的登山者) 我可能没有描述清楚,我的意思不是显示出1-22-25-23-26-21-24-30-2 是通过对allorderid处理后,我order by allorderid 顺序为 1-22-25-23-26-21-24-30-2
select contentid from ContentTable order by AllOrderId
select 0 ContentId, identity(int,1,1) id into #t declare @orderid int declare @cid int set @orderid = 1 while exists (select * from ContentTable where AllOrderId = 0) begin select top 1 @cid = ContentId from #t order by id desc if exists (select ContentId from ContentTable where ContentSort = @cid and AllOrderId = 0) begin select top 1 @cid = ContentId from ContentTable where ContentSort = @cid and AllOrderId = 0 order by orderid insert #t (contentid) values (@cid) update ContentTable set Allorderid = @orderid where contentid = @cid set @orderid = @orderid + 1 end else begin delete #t where contentid = @cid end end select contentid from ContentTable order by AllOrderId drop table #t
to:CrazyFor(太阳下山明朝依旧爬上来)
你的函数只能对id 和 父ID的排序,再加一个OrderId 排序没有体现出来啊
declare @orderid int
declare @cid int
set @orderid = 1
while exists (select * from ContentTable where AllOrderId = 0)
begin
select top 1 @cid = ContentId from #t order by id desc
if exists (select ContentId from ContentTable where ContentSort = @cid and AllOrderId = 0)
begin
select top 1 @cid = ContentId from ContentTable where ContentSort = @cid and AllOrderId = 0 order by orderid
insert #t (contentid) values (@cid)
update ContentTable set Allorderid = @orderid where contentid = @cid
set @orderid = @orderid + 1
end
else
begin
delete #t where contentid = @cid
end
end
declare @s varchar(1000)
set @s = ''
select @s = @s + rtrim(cast(contentid as char))+ '-' from ContentTable order by AllOrderId
select left(@s, len(@s)-1)
drop table #t1-22-25-23-26-21-24-30-2
我可能没有描述清楚,我的意思不是显示出1-22-25-23-26-21-24-30-2
是通过对allorderid处理后,我order by allorderid 顺序为
1-22-25-23-26-21-24-30-2
declare @orderid int
declare @cid int
set @orderid = 1
while exists (select * from ContentTable where AllOrderId = 0)
begin
select top 1 @cid = ContentId from #t order by id desc
if exists (select ContentId from ContentTable where ContentSort = @cid and AllOrderId = 0)
begin
select top 1 @cid = ContentId from ContentTable where ContentSort = @cid and AllOrderId = 0 order by orderid
insert #t (contentid) values (@cid)
update ContentTable set Allorderid = @orderid where contentid = @cid
set @orderid = @orderid + 1
end
else
begin
delete #t where contentid = @cid
end
end
select contentid from ContentTable order by AllOrderId
drop table #t