求一条SQL解决方法

Question

我解释下，表一的ruid_id是空的，我要根据表一的sht_date去表二查到范围内的ruid_id去填充表一的ruid_id

Answer 1

select ruid_id 
from tb1
where exists (select * from tb2
 where tb1.ruid_id=ruid_id and gh=tb1.gh and sht_date>=begin_time and sht_date<=end_time )

Answer 2

update tb1 
set ruid_id=select ruid_id from tb2
 where  gh=tb1.gh and sht_date>=begin_time and sht_date<=end_time )

Answer 3

update 表一 set ruid_id=(select ruid_id from 表二  where 表二.begin_time<=表一.sht_date and 表二.end_time>=表一.sht_date)当表二有多条符合条件的数据的时候该语句会报错，建议采用下列语句
update 表一 set ruid_id=(select max（ruid_id） from 表二  where 表二.begin_time<=表一.sht_date and 表二.end_time>=表一.sht_date)   表二中的符合条件的最大 ruid_id 写入表一，或者把max改成min即为最小值

Answer 4

--> 测试时间:2009-07-09 11:18:00
--> 我的淘宝: http://shop36766744.taobao.com/if object_id('[表1]') is not null drop table [表1]
create table [表1]([gh] int,[sht_date] datetime,[ruid_id] sql_variant)
insert [表1]
select 440,'2009-01-01',null union all
select 440,'2009-01-02',null union all
select 440,'2009-01-07',null union all
select 440,'2009-01-08',null
if object_id('[表二]') is not null drop table [表二]
create table [表二]([gh] int,[ruid_id] varchar(3),[begin_time] datetime,[end_time] datetime)
insert [表二]
select 440,'001','2009-01-01','2009-01-05' union all
select 440,'002','2009-01-06','2009-01-10'
update 表1 set ruid_id=B.ruid_id from 表1 A join 表二 B on A.gh=B.gh and A.sht_date between begin_time and end_timeselect * from 表1
/*
gh          sht_date                                               ruid_id                                                                                                                                                                                                                                                          
----------- ------------------------------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
440         2009-01-01 00:00:00.000                                001
440         2009-01-02 00:00:00.000                                001
440         2009-01-07 00:00:00.000                                002
440         2009-01-08 00:00:00.000                                002（所影响的行数为 4 行）
*/drop table 表1,表二

Answer 5

update 表1 set ruid_id=B.ruid_id from 表1 A,表2 B Where  A.sht_date between B.begin_time and B.end_time

Answer 6

update 表1 set ruid_id=B.ruid_id from 表1 A,表2 B Where A.gh=B.gh And  A.sht_date between B.begin_time and B.end_time

Answer 7

update 表1
set 表1.ruid_id=表2.ruid_id
from 表2
where 表1.gh=表2.gh and 表1.sht_date between 表2.begin_time and 表2.end_time 
-- 含边界值

Answer 8

-- =========================================
-- -----------t_mac 小编-------------
   ---希望有天成为大虾---- 
-- =========================================IF OBJECT_ID('tb1') IS NOT NULL
  DROP TABLE tb1
GO
CREATE TABLE tb1(gh int,sht_date datetime ,ruid_id varchar(10), )
go
insert into tb1
select 
440 ,   '2009-01-01'    ,   ''   union all select 
440  , '2009-01-02'     ,   ''     union all select 
440   , '2009-01-07'    ,     ''    union all select 
440   , '2009-01-08'  ,''
IF OBJECT_ID('tb2') IS NOT NULL
  DROP TABLE tb2
GO
CREATE TABLE tb2(gh int,ruid_id varchar(10),begin_time datetime,end_time datetime )
go
insert into tb2 select 
440  ,  '001'    ,        '2009-01-01'  ,    '2009-01-05'  union all select 
440   , '002'     ,       '2009-01-06'  ,   '2009-01-10'
go update tb1 
set ruid_id=(select ruid_id from tb2
 where  gh=tb1.gh and tb1.sht_date>=begin_time and tb1.sht_date<=end_time )
 select * from tb1/*------------
440 2009-01-01 00:00:00.000 001
440 2009-01-02 00:00:00.000 001
440 2009-01-07 00:00:00.000 002
440 2009-01-08 00:00:00.000 002
-------*/

Answer 9

if object_id('[表一]') is not null drop table [表一]
go
create table [表一]([gh] int,[sht_date] datetime,[ruid_id] varchar(3))
insert [表一]
select 440,'2009-01-01',null union all
select 440,'2009-01-02',null union all
select 440,'2009-01-07',null union all
select 440,'2009-01-08',nullif object_id('[表二]') is not null drop table [表二]
go
create table [表二]([gh] int,[ruid_id] varchar(3),[begin_time] datetime,[end_time] datetime)
insert [表二]
select 440,'001','2009-01-01','2009-01-05' union all
select 440,'002','2009-01-06','2009-01-10'
update [表一]  set [ruid_id]=(select [ruid_id] from [表二] where a.sht_date between [begin_time] and [end_time]) from [表一] aselect * from [表一]--测试结果:
/*
gh      sht_date                 ruid_id
440     2009-01-01 00:00:00.000  001
440     2009-01-02 00:00:00.000  001
440     2009-01-07 00:00:00.000  002
440     2009-01-08 00:00:00.000  002
*/

Answer 10

楼主的意思是sht_date  在begin_time      end_time 之间吧,代码如下:
SELECT a.*,b.sht_date FROM ts2 a  LEFT JOIN ts1 b ON  DATEDIFF(dd,a.begin_time,b.sht_date) >=0 AND  DATEDIFF(dd,b.sht_date,a.end_time) >=0输出:
440 2009-01-01 00:00:00.000 2009-01-05 00:00:00.000 001 2009-01-01 00:00:00.000
440 2009-01-01 00:00:00.000 2009-01-05 00:00:00.000 001 2009-01-02 00:00:00.000
440 2009-01-06 00:00:00.000 2009-01-10 00:00:00.000 002 2009-01-07 00:00:00.000
440 2009-01-06 00:00:00.000 2009-01-10 00:00:00.000 002 2009-01-08 00:00:00.000

Answer 11

另提醒各位菜友一下 sqlserver日期不能直接比较大小,得用DATEDIFF 这个函数

Answer 12

select 表2.ruid_id，表1.ruid_id
from 表2,表1
where 表1.ruid_id>=表2.begin_time and 表1.ruid_id<=表2.end_time 如果日期字段有时间的话，就在前面加上datediff