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

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

原表结果：
IdTitleCreationTimeOrderIndex
1文章A2010-03-19 14:32:29.6530
2文章B2010-03-19 14:32:29.6530
3文章C2010-03-19 14:32:29.6532
4文章D2010-03-19 14:32:29.6530
5文章E2010-03-19 14:32:29.6536
6文章F2010-03-19 14:32:29.6533
7文章G2010-03-19 14:32:29.6530
8文章H2010-03-19 14:32:29.6534
9文章I2010-03-19 14:32:29.6530

输出后结果：
IdTitleCreationTimeOrderIndex
1文章A2010-03-19 14:32:29.6530
3文章C2010-03-19 14:32:29.6532
6文章F2010-03-19 14:32:29.6533
8文章H2010-03-19 14:32:29.6534
2文章B2010-03-19 14:32:29.6530
5文章E2010-03-19 14:32:29.6536
4文章D2010-03-19 14:32:29.6530
7文章G2010-03-19 14:32:29.6530
9文章I2010-03-19 14:32:29.6530

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)  
 
 insert into #t(title,orderIndex) values('文章I',0) 
  
  
 select distinct  number,#t.*  into #basic 
 from master..spt_values a 
 left join #t 
 on  number=orderIndex 
 where number between 1 and (select max(orderIndex) from #t) 
  
 select  isnull(X1.id,X2.id) as ID, 
     isnull(X1.title,X2.title) as title, 
      isnull(X1.CreationTime,X2.CreationTime) as CreationTime, 
      isnull(X1.orderIndex ,X2.orderIndex) as orderIndex 
 from 
 ( 
 select tmp=(select sum(case when ID is null then 1 else 0 end)  from #basic where number <=A.number and ID is null),* 
 from #basic as A 
 ) X1 
 full join 
  ( select  tmp=(select sum(case when OrderIndex=0 then 1 else 0 end) from #t where id <=A.id),*  
  from #t as A where orderIndex=0 
 ) X2 
 on  X1.tmp=X2.tmp and X1.ID is null 
  
 order by  case when X1.number is not null then 1 else 2 end, 
        X1.number, 
        X2.ID 
  
 /* 
 1文章A2010-03-19 16:02:10.6230 
 3文章C2010-03-19 16:02:10.6232 
 6文章F2010-03-19 16:02:10.6233 
 8文章H2010-03-19 16:02:10.6234 
 2文章B2010-03-19 16:02:10.6230 
 5文章E2010-03-19 16:02:10.6236 
 4文章D2010-03-19 16:02:10.6230 
 7文章G2010-03-19 16:02:10.6230 
 9文章I2010-03-19 16:02:10.6230 
  
 */ 
  
 Drop table #t,#basic 
 

查看更多 下一篇