给你一个类似的参考:
树排序:
ContentId ContentSort OrderId AllOrderId
----------- ----------- ----------- -----------
1 0 0 0
2 0 1 0
21 1 2 0
22 1 0 0
23 1 1 0
24 21 0 0
25 22 1 0
26 23 2 0
30 21 1 0select 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 #t
树排序:
ContentId ContentSort OrderId AllOrderId
----------- ----------- ----------- -----------
1 0 0 0
2 0 1 0
21 1 2 0
22 1 0 0
23 1 1 0
24 21 0 0
25 22 1 0
26 23 2 0
30 21 1 0select 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 #t
解决方案 »
- 一个看似简单但是头疼的sql问题
- 请教 安装Sql Server 2005 选择的组件都是做什么的啊?
- 关于安装sQL的问题,我的系统是2003
- 怎么查询一个数据库里的有哪些表,试图,约束等??
- sql server 语句中local什么意思?
- 求助,数据如何拆分?
- 如何连接两个长度为8000,类型是varchar的字符串为ntext或其他类型的可读类型?
- where to find the key for SQL-programmer ?
- 为了使用sql server,我下在了opta2000.jar,程序编译通过,可是报错:db.executeQuery: No suitable driver,请问该怎么设置?
- 下列情况如何实现用户要求(关于数据库)?
- 如何在2个数据库的表间复制大数据量的表
- 大家帮我看一下这SQL 为什么达不到我想要的效果
Sub_BelongTo
加索引,参考:
http://expert.csdn.net/Expert/topic/1375/1375432.xml?temp=.8570978
为什么父编码是Varchar(13),而本级代码却是Varchar(2)?能说明一下吗?