SQL益智题目，有点难度！解决方法_Sql Server

SQL益智题目，有点难度！！！！

SQL code

CREATE TABLE #t(    [Id] [int] IDENTITY(1,1) NOT NULL,    [Title] [nvarchar](50),    [CreationTime] [datetime] DEFAULT (getdate()),    [OrderIndex] [int],) go insert into #t(title,orderIndex) values('文章A',0)insert into #t(title,orderIndex) values('文章B',0)insert into #t(title,orderIndex) values('文章C',2)insert into #t(title,orderIndex) values('文章D',0)insert into #t(title,orderIndex) values('文章E',6)insert into #t(title,orderIndex) values('文章F',3)insert into #t(title,orderIndex) values('文章G',0)insert into #t(title,orderIndex) values('文章H',4)insert into #t(title,orderIndex) values('文章I',0)select * from #t

原表结果：
Id Title CreationTime OrderIndex
1 文章A 2010-03-19 14:32:29.653 0
2 文章B 2010-03-19 14:32:29.653 0
3 文章C 2010-03-19 14:32:29.653 2
4 文章D 2010-03-19 14:32:29.653 0
5 文章E 2010-03-19 14:32:29.653 6
6 文章F 2010-03-19 14:32:29.653 3
7 文章G 2010-03-19 14:32:29.653 0
8 文章H 2010-03-19 14:32:29.653 4
9 文章I 2010-03-19 14:32:29.653 0

输出后结果：
Id Title CreationTime OrderIndex
1 文章A 2010-03-19 14:32:29.653 0
3 文章C 2010-03-19 14:32:29.653 2
6 文章F 2010-03-19 14:32:29.653 3
8 文章H 2010-03-19 14:32:29.653 4
2 文章B 2010-03-19 14:32:29.653 0
5 文章E 2010-03-19 14:32:29.653 6
4 文章D 2010-03-19 14:32:29.653 0
7 文章G 2010-03-19 14:32:29.653 0
9 文章I 2010-03-19 14:32:29.653 0

PS: OrderIndex的相应值指定输出到某行，如OrderIndex=2 该记录对应输出到第2行，＝6，则记录输出到第6行，其它OrderIndex=0则默认按id降序。

------解决方案--------------------
这个需求,不好做.得用循环或游标.帮顶.
------解决方案--------------------
这个太益智了...
------解决方案--------------------

探讨
SQL code
--DROP TABLE #T
CREATE TABLE #t(
[Id] [int] IDENTITY(1,1) NOT NULL,
[Title] [nvarchar](50),
[CreationTime] [datetime] DEFAULT (getdate()),
[OrderIndex] [int],
)

go ……

------解决方案--------------------
刚才的不对,SORRY.
这个运行过了的:
select rowId,Id,Title,CreationTime,OrderIndex
from
(
select rowId = id,rn = Row_Number() over(order by id) from #t where ID NOT IN (select orderindex from #t)
) A INNER JOIN
(
select *,rn = Row_Number() over(order by id) from #t where OrderIndex=0
) B ON A.rn=B.rn
UNION
select OrderIndex,* from #t where OrderIndex>0
order by 1
------解决方案--------------------

探讨
刚才的不对,SORRY.
这个运行过了的:
select rowId,Id,Title,CreationTime,OrderIndex
from
(
select rowId = id,rn = Row_Number() over(order by id) from #t where ID NOT IN (select orderindex from #t)
) A INNER JOI……

------解决方案--------------------

SQL code

 
 CREATE TABLE #t( 
   [Id] [int] IDENTITY(1,1) NOT NULL, 
   [Title] [nvarchar](50), 
   [CreationTime] [datetime] DEFAULT (getdate()), 
   [OrderIndex] [int], 
 )  
  
 go  
  
 insert into #t(title,orderIndex) values('文章A',0) 
 insert into #t(title,orderIndex) values('文章B',0) 
 insert into #t(title,orderIndex) values('文章C',2) 
 insert into #t(title,orderIndex) values('文章D',0) 
 insert into #t(title,orderIndex) values('文章E',6) 
 insert into #t(title,orderIndex) values('文章F',3) 
 insert into #t(title,orderIndex) values('文章G',0) 
 insert into #t(title,orderIndex) values('文章H',4)