日期:2014-05-18  浏览次数:20502 次

一个比较别扭的查询大家来帮忙啊!
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
请大家帮下忙啊,很急啊!

------解决方案--------------------
SQL code
--> 测试数据:[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]

------解决方案--------------------
--你这个我建议使用一个序号,然后根据序号来提前每个时间的先后.
SQL code
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)
go

select 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.time

drop 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