求存储过程

Question

表1                                               表2
systime               a        b                 systime                c              b 
2009-12-1 1:00:00             <null>             2009-12-1 1:23:00                     38
2009-12-1 2:00:00             <null>             2009-12-1 1:56:00                     38 
2009-12-1 3:00:00             <null>             2009-12-1 2:23:00                     38.5 
2009-12-1 4:00:00             <null>             2009-12-1 2:56:00                     38.5
2009-12-1 5:00:00             <null>             2009-12-1 3:23:00                     39
2009-12-1 6:00:00             <null>             2009-12-1 3:56:00                     39
2009-12-1 7:00:00             <null>             2009-12-1 4:23:00                     38 
....                                             2009-12-1 4:56:00                     38 
2009-12-1 24:00:00            <null>             ....
......                                           2009-12-1 24:26:00                    38 
                                                 2009-12-1 24:56:00                    38 
                                                 .....
求存储过程 表2中的systime中,在同一个小时的时段内，只取一条数据，将表2中的b插入表1的b中，

Answer 1

insert into
 表1(b) 
select
 b 
from
 表2 t 
where 
systime=(select min(systime) from 表2 where datepart(hh,systime)=datepart(hh,t.systime))

Answer 2

--这样准确点
insert into
 表1(b) 
select
 b 
from
 表2 t 
where 
 systime=(select min(systime) from 表2 where datepart(hh,systime)=datepart(hh,t.systime) and datediff(dd,systime,t.systime)=0)

Answer 3

update t1
set b=
(select top 1 b from t2
 where datediff(hh,t1.systime,t2.systime)=0 
 order by systime desc
)

Answer 4

--这样
insert into
 表1(b) 
select
 b 
from
 表2 t 
where 
 systime=(select min(systime) from 表2 where datediff(hh,systime,t.systime)=0)

Answer 5

if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (systime datetime,b numeric(3,1))
insert into [tb]
select '2009-12-1 1:23:00',38 union all
select '2009-12-1 1:56:00',38 union all
select '2009-12-1 2:23:00',38.5 union all
select '2009-12-1 2:56:00',38.5 union all
select '2009-12-1 3:23:00',39 union all
select '2009-12-1 3:56:00',39 union all
select '2009-12-1 4:23:00',38 union all
select '2009-12-1 4:56:00',38
select convert(varchar(13),systime,20)+':00:00'systime,
       b
from tb t 
where not exists(select 1 from tb where datediff(hour,systime,t.systime)=0 and systime>t.systime)
/*
systime             b
------------------- ---------------------------------------
2009-12-01 01:00:00 38.0
2009-12-01 02:00:00 38.5
2009-12-01 03:00:00 39.0
2009-12-01 04:00:00 38.0(4 個資料列受到影響)*/

Answer 6

update t1
set b=
(select top 1 b from t2
 where datediff(hh,t1.systime,t2.systime)=0 
 order by systime desc
)