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 allselect '2012-03-28 12:00:00',0.29,59.60,63.00,71.50 union allselect '2012-03-28 16:00:00',0.29,59.60,62.40,71.10 union allselect '2012-03-28 20:00:00',0.23,59.10,62.30,71.40 union allselect '2012-03-28 08:00:00',0.29,56.80,59.60,69.60 union allselect '2012-03-28 12:00:00',0.29,56.60,59.30,69.10 union allselect '2012-03-28 16:00:00',0.27,56.60,59.30,69.90 union allselect '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)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)) mfull join(select t.* from tb t where id = (select max(id) from tb where time = t.time)) non 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.602012-03-28 16:00:00.000 .29 59.60 62.40 71.10 .27 56.60 59.30 69.902012-03-28 12:00:00.000 .29 59.60 63.00 71.50 .29 56.60 59.30 69.102012-03-28 08:00:00.000 .23 59.50 62.40 71.70 .29 56.80 59.60 69.60（所影响的行数为 4 行）*/