一个比较别扭的查询大家来帮忙啊！

Question

time                     a        b       c       d
2012-03-28 08:00:00 0.23 59.50 62.40 71.70
2012-03-28 12:00:00 0.29 59.60 63.00 71.50
2012-03-28 16:00:00 0.29 59.60 62.40 71.10
2012-03-28 20:00:00 0.23 59.10 62.30 71.40
2012-03-28 08:00:00 0.29 56.80 59.60 69.60
2012-03-28 12:00:00 0.29 56.60 59.30 69.10
2012-03-28 16:00:00 0.27 56.60 59.30 69.90
2012-03-28 20:00:00 0.21 56.20 59.30 69.60
想要得到如下
time                     a        b       c       d     e         f       g      h
2012-03-28 08:00:00 0.23 59.50 62.40 71.70  0.29 56.80 59.60 69.60
2012-03-28 12:00:00 0.29 59.60 63.00 71.50  0.29 56.60 59.30 69.10
2012-03-28 16:00:00 0.29 59.60 62.40 71.10  0.27 56.60 59.30 69.90
2012-03-28 20:00:00 0.23 59.10 62.30 71.40  0.21 56.20 59.30 69.60
请大家帮下忙啊，很急啊！

Answer 1

--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([time] datetime,[a] numeric(3,2),[b] numeric(4,2),[c] numeric(4,2),[d] numeric(4,2))
insert [tb]
select '2012-03-28 08:00:00',0.23,59.50,62.40,71.70 union all
select '2012-03-28 12:00:00',0.29,59.60,63.00,71.50 union all
select '2012-03-28 16:00:00',0.29,59.60,62.40,71.10 union all
select '2012-03-28 20:00:00',0.23,59.10,62.30,71.40 union all
select '2012-03-28 08:00:00',0.29,56.80,59.60,69.60 union all
select '2012-03-28 12:00:00',0.29,56.60,59.30,69.10 union all
select '2012-03-28 16:00:00',0.27,56.60,59.30,69.90 union all
select '2012-03-28 20:00:00',0.21,56.20,59.30,69.60
--------------开始查询--------------------------
;with cte as
(
select *,new_id=row_number() over(partition by time order by [time]) from [tb]
)
select * from cte a, cte b  where a.new_id=b.new_id-1  and a.[time]= b.[time]

Answer 2

--你这个我建议使用一个序号,然后根据序号来提前每个时间的先后.
create table tb(id int, time datetime,a decimal(18,2),b decimal(18,2),c decimal(18,2),d decimal(18,2))
insert into tb values(1,'2012-03-28 08:00:00', 0.23 ,59.50 ,62.40 ,71.70)
insert into tb values(2,'2012-03-28 12:00:00', 0.29 ,59.60 ,63.00 ,71.50)
insert into tb values(3,'2012-03-28 16:00:00', 0.29 ,59.60 ,62.40 ,71.10)
insert into tb values(4,'2012-03-28 20:00:00', 0.23 ,59.10 ,62.30 ,71.40)
insert into tb values(5,'2012-03-28 08:00:00', 0.29 ,56.80 ,59.60 ,69.60)
insert into tb values(6,'2012-03-28 12:00:00', 0.29 ,56.60 ,59.30 ,69.10)
insert into tb values(7,'2012-03-28 16:00:00', 0.27 ,56.60 ,59.30 ,69.90)
insert into tb values(8,'2012-03-28 20:00:00', 0.21 ,56.20 ,59.30 ,69.60)
goselect isnull(m.time,n.time) time,m.a,m.b,m.c,m.d,n.a e,n.b f,n.c g,n.d h from
(select t.* from tb t where id = (select min(id) from tb where time = t.time)) m
full join
(select t.* from tb t where id = (select max(id) from tb where time = t.time)) n
on m.time = n.timedrop table tb/*
time                                                   a                    b                    c                    d                    e                    f                    g                    h                    
------------------------------------------------------ -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- 
2012-03-28 20:00:00.000                                .23                  59.10                62.30                71.40                .21                  56.20                59.30                69.60
2012-03-28 16:00:00.000                                .29                  59.60                62.40                71.10                .27                  56.60                59.30                69.90
2012-03-28 12:00:00.000                                .29                  59.60                63.00                71.50                .29                  56.60                59.30                69.10
2012-03-28 08:00:00.000                                .23                  59.50                62.40                71.70                .29                  56.80                59.60                69.60（所影响的行数为 4 行）
*/--如果你没有那个序号，在sql 2000中需要使用临时表来完成
create table tb(time datetime,a decimal(18,2),b decimal(18,2),c decimal(18,2),d decimal(18,2))
insert into tb values('2012-03-28 08:00:00', 0.23 ,59.50 ,62.40 ,71.70)
insert into tb values('2012-03-28 12:00:00', 0.29 ,59.60 ,63.00 ,71.50)
insert into tb values('2012-03-28 16:00:00', 0.29 ,59.60 ,62.40 ,71.10)
insert into tb values('2012-03-28 20:00:00', 0.23 ,59.10 ,62.30 ,71.40)
insert into tb values('2012-03-28 08:00:00', 0.29 ,56.80 ,59.60 ,69.60)
insert into tb values('2012-03-28 12:00:00', 0.29 ,56.60 ,59.30 ,69.10)
insert into tb values('2012-03-28 16:00:00', 0.27 ,56.60 ,59.30 ,69.90)
insert into tb values('2012-03-28 20:00:00', 0.21 ,56.20 ,59.30 ,69.60)
go
--生成临时表
select id=identity(int,1,1) , * into tmp from tbselect isnull(m.time,n.time) time,m.a,m.b,m.c,m.d,n.a e,n.b f,n.c g,n.d h from
(select t.* from tmp t where id = (select min(id) from tmp where time = t.time)) m
full join
(select t.* from tmp t where id = (select max(id) from tmp where time = t.time)) n
on m.time = n.timedrop table tb,tmp/*
time                                                   a                    b                    c                    d                    e                    f                    g                    h                    
------------------------------------------------------ -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- 
2012-03-28 20:00:00.000                                .23                  59.10                62.30                71.40                .21                  56.20                59.30                69.60
2012-03-28 16:00:00.000                                .29                  59.60                62.40                71.10                .27                  56.60                59.30                69.90
2012-03-28 12:00:00.000                                .29                  59.60                63.00                71.50                .29                  56.60                59.30                69.10
2012-03-28 08:00:00.000                                .23                  59.50                62.40                71.70                .29                  56.80                59.60                69.60（所影响的行数为 4 行）
*/--如果是sql 2005,可使用rownumber()来实现:
create table tb(time datetime,a decimal(18,2),b decimal(18,2),c decimal(18,2),d decimal(18,2))
insert into tb values('2012-03-28 08:00:00', 0.23 ,59.50 ,62.40 ,71.70)
insert into tb values('2012-03-28 12:00:00', 0.29 ,59.60 ,63.00 ,71.50)
insert into tb values('2012-03-28 16:00:00', 0.29 ,59.60 ,62.40 ,71.10)
insert into tb values('2012-03-28 20:00:00', 0.23 ,59.10 ,62.30 ,71.40)
insert into tb values('2012-03-28 08:00:00', 0.29 ,56.80 ,59.60 ,69.60)
insert into tb values('2012-03-28 12:00:00', 0.29 ,56.60 ,59.30 ,69.10)
insert into tb values('2012-03-28 16:00:00', 0.27 ,56.60 ,59.30 ,69.90)
insert into tb values('2012-03-28 20:00:00', 0.21 ,56.20 ,59.30 ,69.60)
go
select time,
       max(case id when 1 then a end) a,
       max(case id when 1 then b end) b,
       max(case id when 1 then c end) c,
       max(case id when 1 then d end) d,
       max(case id when 2 then a end) e,
       max(case id when 2 then b end) f,
       max(case id when 2 then c end) g,
       max(case id when 2 then d end) h
from
(
  select id = row_number() over(partition by time order by time),* from tb
) t
group by timedrop table tb/*
time                    a                                       b                                       c                                       d                                       e                                       f                                       g                                       h
----------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
2012-03-28 08:00:00.000 0.23                                    59.50                                   62.40                                   71.70                                   0.29                                    56.80                                   59.60                                   69.60
2012-03-28 12:00:00.000 0.29                                    56.60                                   59.30                                   69.10                                   0.29                                    59.60                                   63.00                                   71.50
2012-03-28 16:00:00.000 0.29                                    59.60                                   62.40                                   71.10                                   0.27                                    56.60                                   59.30                                   69.90
2012-03-28 20:00:00.000 0.21                                    56.20                                   59.30                                   69.60                                   0.23                                    59.10                                   62.30                                   71.40
警告: 聚合或其他 SET 操作消除了空值。(4 行受影响)
*/