怎样查出这样的数据？？、有点难度，想了好久，没好的思路..

Question

--测试数据
DECLARE @table TABLE(Name  CHAR(10),CREATETIME DATETIME,OrderIndex INT)
INSERT INTO @table
SELECT 'aa',DATEADD(HH,3,GETDATE()),1 UNION ALL
SELECT 'cc',DATEADD(HH,2,GETDATE()),NULL UNION ALL
SELECT 'DD',DATEADD(HH,1,GETDATE()),NULL UNION ALL
SELECT 'EE',DATEADD(HH,5,GETDATE()),NULL UNION ALL
SELECT 'tt',DATEADD(HH,4,GETDATE()),3--直接查询排序为如下数据：
SELECT * FROM @table ORDER BY createtime 
/*
(5 行受影响)
Name       CREATETIME              OrderIndex
---------- ----------------------- -----------
DD         2011-07-27 21:56:50.227 NULL
cc         2011-07-27 22:56:50.227 NULL
aa         2011-07-27 23:56:50.227 1
tt         2011-07-28 00:56:50.227 3
EE         2011-07-28 01:56:50.227 NULL
*/
--想得这样的结果
/*
Name       CREATETIME              OrderIndex
---------- ----------------------- -----------
DD         2011-07-27 21:56:50.227 2
cc         2011-07-27 22:56:50.227 4
aa         2011-07-27 23:56:50.227 1
tt         2011-07-28 00:56:50.227 3
EE         2011-07-28 01:56:50.227 5
*/说明：按照时间来排序给出序号，但是OrderIndex有值的情况下，先考虑OrderIndex
就像上面给出的数据：2011-07-27 23:56:50.227 这条OrderIndex 是1 所以 最早的这一条(2011-07-27 21:56:50.227)OrderIndex 就只能回填2了.

Answer 1

--测试数据
create table tb(name  char(10),createtime datetime,orderindex int)
insert into tb
select 'aa',dateadd(hh,3,getdate()),1 union all
select 'cc',dateadd(hh,2,getdate()),null union all
select 'dd',dateadd(hh,1,getdate()),null union all
select 'ee',dateadd(hh,5,getdate()),null union all
select 'tt',dateadd(hh,4,getdate()),3
go;with ac1 as
(
select distinct number,rid=row_number() over (order by getdate())
from master..spt_values
where number between 1 and (select count(*) from tb)
and number not in (select orderindex from tb where orderindex is not null)
and [type] = 'p'
),ac2 as
(
select *,px=row_number() over (order by createtime)
from tb
where orderindex is null
)select a.name,a.createtime,b.number
from ac2 a join ac1 b on a.px = b.rid
union all
select *
from tb
where orderindex is not nulldrop table tb
/**************name       createtime              number
---------- ----------------------- -----------
dd         2011-07-27 22:59:37.497 2
cc         2011-07-27 23:59:37.497 4
ee         2011-07-28 02:59:37.497 5
aa         2011-07-28 00:59:37.497 1
tt         2011-07-28 01:59:37.497 3(5 行受影响)

Answer 2

1楼的实现思路：如果不考虑orderindex，那么排序是从1开始直接记录数为止。那么在考虑orderindex时，借助系统表将从1到这个表的记录数的所有计数罗列出来，将其中在表里orderindex这个字段存在的去除，从小到大做个标记列。将表中orderindex为null的按日期升序做标记，是为了和之前计数表统一。然后将两个表按计数列合并，当然这些都是orderindex在原表为null的数据，查询出结果集后再将原表orderindex不为null的union all，得到新的排序。

Answer 3

declare @table table(name  char(10),createtime datetime,orderindex int)
insert into @table
select 'aa',dateadd(hh,3,getdate()),1 union all
select 'cc',dateadd(hh,2,getdate()),null union all
select 'dd',dateadd(hh,1,getdate()),null union all
select 'ee',dateadd(hh,5,getdate()),null union all
select 'tt',dateadd(hh,4,getdate()),3select * from @table where orderindex is not null
union all select aa.name,aa.createtime,bb.number from 
(select row_number() over (order by createtime) as rid,* from @table 
where orderindex is null) aa 
left join (select row_number() over (order by number) as rid,b.number from 
master..spt_values b where number between 1 and 
(select count(1) from @table ) and [type]='P'
and b.number not in (select orderindex from @table where orderindex is not null)) bb
on aa.rid=bb.rid order by createtime
/*
name       createtime              orderindex
---------- ----------------------- -----------
dd         2011-07-27 23:15:49.310 2
cc         2011-07-28 00:15:49.310 4
aa         2011-07-28 01:15:49.310 1
tt         2011-07-28 02:15:49.310 3
ee         2011-07-28 03:15:49.310 5
*/--应该有简单的方法，但是我暂时想不到

Answer 4

DECLARE @table TABLE(Name  CHAR(10),CREATETIME DATETIME,OrderIndex INT)
INSERT INTO @table
SELECT 'aa',DATEADD(HH,3,GETDATE()),1 UNION ALL
SELECT 'cc',DATEADD(HH,2,GETDATE()),NULL UNION ALL
SELECT 'DD',DATEADD(HH,1,GETDATE()),NULL UNION ALL
SELECT 'EE',DATEADD(HH,5,GETDATE()),NULL UNION ALL
SELECT 'tt',DATEADD(HH,4,GETDATE()),3
select t1.name,t1.createtime,(case when t1.orderindex is null then t3.number else t1.orderindex end)OrderIndex
from @table t1 left join(
select row_number()over(order by orderindex)rn,* from @table where orderindex is null
)t2 on t1.name=t2.name and t1.createtime=t2.createtime left join(
select row_number()over(order by number)rn,number from master..spt_values a where type='p' and number>0 and number<=(select count(*) from @table)
and not exists(select 1 from @table where orderindex=a.number)
)t3 on t2.rn=t3.rn
order by createtime
/*
name       createtime              OrderIndex
---------- ----------------------- -----------
DD         2011-07-27 23:31:47.670 4
cc         2011-07-28 00:31:47.670 2
aa         2011-07-28 01:31:47.670 1
tt         2011-07-28 02:31:47.670 3
EE         2011-07-28 03:31:47.670 5(5 行受影响)
*/

Answer 5

create TABLE xx(Name  CHAR(10),CREATETIME DATETIME,OrderIndex INT)
INSERT INTO xx
SELECT 'aa',DATEADD(HH,3,GETDATE()),1 UNION ALL
SELECT 'cc',DATEADD(HH,2,GETDATE()),NULL UNION ALL
SELECT 'DD',DATEADD(HH,1,GETDATE()),NULL UNION ALL
SELECT 'EE',DATEADD(HH,5,GETDATE()),NULL UNION ALL
SELECT 'tt',DATEADD(HH,4,GETDATE()),3
select identity(int,1,1) as id,createtime  into #x from xx
select id,a.createtime acreatetime,name,b.createtime bcreatetime,orderindex into #y  from #x a left join xx b on  id=orderindex
declare @a int
declare @createtime datetime
set @a=1
while @a<=5
begin
if @a<>1 and @a<>3
begin
select @createtime=(select min(acreatetime) from #y where acreatetime not in(select bcreatetime from #y where bcreatetime is not null))
update #y set bcreatetime =@createtime ,orderindex=@a where id=@a
end
set @a=@a+1
end

Answer 6

;with ac1 as
(
    select distinct number,rid=row_number() over (order by getdate())
    from master..spt_values
    where number between 1 and (select count(*) from tb)
        and number not in (select orderindex from tb where orderindex is not null)
        and [type] = 'p'
),ac2 as
(
    select *,px=row_number() over (order by createtime)
    from tb
    where orderindex is null
)select a.name,a.createtime,b.number
from ac2 a join ac1 b on a.px = b.rid
union all
select *
from tb
where orderindex is not nullselect * from tb where orderindex is not null
union all select aa.name,aa.createtime,bb.number from 
(select row_number() over (order by createtime) as rid,* from tb 
where orderindex is null) aa 
left join (select row_number() over (order by number) as rid,b.number from 
master..spt_values b where number between 1 and 
(select count(1) from tb ) and [type]='P'
and b.number not in (select orderindex from tb where orderindex is not null)) bb
on aa.rid=bb.rid order by createtimeselect t1.name,t1.createtime,(case when t1.orderindex is null then t3.number else t1.orderindex end)OrderIndex
from tb t1 left join(
select row_number()over(order by orderindex)rn,* from tb where orderindex is null
)t2 on t1.name=t2.name and t1.createtime=t2.createtime left join(
select row_number()over(order by number)rn,number from master..spt_values a where type='p' and number>0 and number<=(select count(*) from tb)
and not exists(select 1 from tb where orderindex=a.number)
)t3 on t2.rn=t3.rn
order by createtime1楼开销 占 35%
3楼开销 占 32%
4楼开销 占 33%

Answer 7

修改:
1楼开销占38%
3楼开销占34%
本楼开销占 28%
create table tb(name  char(10),createtime datetime,orderindex int)
insert into tb
select 'aa',dateadd(hh,3,getdate()),1 union all
select 'cc',dateadd(hh,2,getdate()),null union all
select 'dd',dateadd(hh,1,getdate()),null union all
select 'ee',dateadd(hh,5,getdate()),null union all
select 'tt',dateadd(hh,4,getdate()),3
goselect name,createtime,orderindex from tb where orderindex is not null
union all
select t2.name,t2.createtime,t3.number from(
select row_number()over(order by orderindex)rn,* from tb where orderindex is null
)t2 inner join(
select row_number()over(order by number)rn,number from master..spt_values a where type='p' and number>0 and number<=(select count(*) from tb)
and not exists(select 1 from tb where orderindex=a.number)
)t3 on t2.rn=t3.rn
order by createtime
/*
name       createtime              orderindex
---------- ----------------------- -----------
dd         2011-07-27 23:33:55.123 4
cc         2011-07-28 00:33:55.123 2
aa         2011-07-28 01:33:55.123 1
tt         2011-07-28 02:33:55.123 3
ee         2011-07-28 03:33:55.123 5(5 行受影响)
*/