变态的排序问题

http://topic.csdn.net/u/20100319/15/86081abf-ed59-49a1-909a-ccf57c9828fb.html?96862

 

原表结果：
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降序。

--------------------------------------------------------------------------------------------------------------------------------------------------------

DROP TABLE #T DROP TABLE #T2 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) /* 1 文章A 2010-03-19 15:49:54.793 0 2 文章B 2010-03-19 15:49:54.793 0 3 文章C 2010-03-19 15:49:54.793 2 4 文章D 2010-03-19 15:49:54.793 0 5 文章E 2010-03-19 15:49:54.793 6 6 文章F 2010-03-19 15:49:54.793 3 7 文章G 2010-03-19 15:49:54.793 0 8 文章H 2010-03-19 15:49:54.793 4 9 文章I 2010-03-19 15:49:54.793 0 */ CREATE TABLE #t2( [NID] INT, [NID2] INT, [Id] [int], [Title] [nvarchar](50), [CreationTime] [datetime] DEFAULT (getdate()), [OrderIndex] [int], ) INSERT INTO #T2(NID2) SELECT ROW_NUMBER() OVER (ORDER BY ID ASC) FROM #T DECLARE @I INT SELECT @I=0 UPDATE T2 SET T2.ID=T1.ID,T2.TITLE=T1.TITLE,T2.CREATIONTIME=T1.CREATIONTIME,T2.ORDERINDEX=T1.ORDERINDEX ,NID=CASE WHEN T1.ORDERINDEX IS NOT NULL THEN NULL ELSE @I END ,@I=CASE WHEN T1.ORDERINDEX IS NOT NULL THEN @I ELSE @I+1 END FROM #T2 T2 LEFT JOIN #T T1 ON T2.NID2=T1.ORDERINDEX UPDATE T2 SET T2.ID=T1.ID,T2.TITLE=T1.TITLE,T2.CREATIONTIME=T1.CREATIONTIME,T2.ORDERINDEX=T1.ORDERINDEX FROM #T2 T2 INNER JOIN ( SELECT ROW_NUMBER() OVER (ORDER BY ID ASC) 'NID',* FROM #T WHERE ORDERINDEX=0 ) T1 ON T2.NID=T1.NID SELECT ID,TITLE,CREATIONTIME,ORDERINDEX FROM #T2 /* ID TITLE CREATIONTIME ORDERINDEX ----------- -------------------------------------------------- ----------------------- ----------- 1 文章A 2010-03-19 16:55:43.967 0 3 文章C 2010-03-19 16:55:43.967 2 6 文章F 2010-03-19 16:55:43.967 3 8 文章H 2010-03-19 16:55:43.967 4 2 文章B 2010-03-19 16:55:43.967 0 5 文章E 2010-03-19 16:55:43.967 6 4 文章D 2010-03-19 16:55:43.967 0 7 文章G 2010-03-19 16:55:43.967 0 9 文章I 2010-03-19 16:55:43.967 0 */