请教一个sqlserver的问题
两张表table1,table2,它们之间用numb关联table1
numb(varchar) time1(datatime) time2(datatime)
------------------------------------------------------------
s1 2008-7-29 12:00:00 2008-7-29 12:30:00
s2 2008-7-29 12:00:00 2008-7-29 13:00:00table2
numb(varchar) sig(varchar)
-----------------------------------
s1 0
s2 0查询table1中time1与time2相差时间如果大于或者等于60分钟,将table2中numb对应的sig修改为“是”,如果小于60分钟,将table2中numb对应的sig修改为“否”请大家帮助一下,谢谢!
两张表table1,table2,它们之间用numb关联table1
numb(varchar) time1(datatime) time2(datatime)
------------------------------------------------------------
s1 2008-7-29 12:00:00 2008-7-29 12:30:00
s2 2008-7-29 12:00:00 2008-7-29 13:00:00table2
numb(varchar) sig(varchar)
-----------------------------------
s1 0
s2 0查询table1中time1与time2相差时间如果大于或者等于60分钟,将table2中numb对应的sig修改为“是”,如果小于60分钟,将table2中numb对应的sig修改为“否”请大家帮助一下,谢谢!
from table2 a left join table1 b on a.numb=b.numb
select * from table2
-------
s1 否
s2 是
set t2.sig = (case when datediff(n,t1.time1,t1.time2)>=60 then '是' else '否' end)
from tb2 t2
inner join tb1 t1 on t1.numb = t2.numb
from table2 a
create table table1
(
numb varchar(10),
time1 datetime,
time2 datetime
)create table table2
(
numb varchar(10),
sig varchar(10)
)
goinsert into table1
select 's1', '2008-7-29 12:00:00', '2008-7-29 12:30:00' union all
select 's2', '2008-7-29 12:00:00', '2008-7-29 13:00:00'insert into table2
select 's1', '0' union all
select 's2', '0'update table2
set sig = case
when DateDiff(minute, a.time1, a.time2) >= 60 then '是'
else '否'
end
from table2 b, table1 a
where a.numb = b.numb