create table #test(id int,att_dt datetime,info nvarchar(100))
insert into #test
select 1,'2011-4-5','' union all
select 2,'2011-4-6','' union all
select 3,'2011-4-7','' union all
select 4,'2011-4-8','' union all
select 5,'2011-4-9' ,''create table #test1(id int,att_dt datetime,info nvarchar(100))
insert into #test1
select 1,'2011-4-5','aaaaaaa' union all
select 2,'2011-4-6','bbbbbbbb' union all
select 3,'2011-4-7','ccccccc' union all
select 4,'2011-4-8','ddddddd' union all
select 5,'2011-4-9' ,'eeeeee'--得到结果
--更新#test后的结果应该为
--id----att_dt---info--
--1 2011-4-5 null
--2 2011-4-6 aaaaaaa
--3 2011-4-7 bbbbbbbb
--4 2011-4-8 ccccccc--在#test1中找到#test中att_dt下一天info更新到#test
解决方案 »
- 谁帮优化下SQL语句
- 如何用alter修改一個Table裏面的default值
- 向数据表中插入一幅图片
- 根据汉字拼音首字母,英文单词首字母排序
- 这个SQl语句怎么写?
- 求助! 关于输出日期的问题!
- 很棘手的分页SQL语句中的ORDER BY clause (Item) conflicts with DISTINCT
- 急。对数据库的一个表做了误操作。update的时候没加where.致使这个表里几十条记录全部成了一条。以前的不记得了。
- 关于主键的设置
- 请教SQL:字符、日期、整型字段怎么连接?
- SQL server2000 如何实现异地服务器商品资料数据同步啊?比较新手
- [求解]以某一日期来按月份分类数据
set info=#test1.info
from #test s join #test2 t on s.att_dt =t.att_dt +1
set info =b.info
from #test as a
inner join #test1 as b on a.att_dt=b.att_dt-1
insert into #test
select 1,'2011-4-5','' union all
select 2,'2011-4-6','' union all
select 3,'2011-4-7','' union all
select 4,'2011-4-8','' union all
select 5,'2011-4-9' ,''create table #test1(id int,att_dt datetime,info nvarchar(100))
insert into #test1
select 1,'2011-4-5','aaaaaaa' union all
select 2,'2011-4-6','bbbbbbbb' union all
select 3,'2011-4-7','ccccccc' union all
select 4,'2011-4-8','ddddddd' union all
select 5,'2011-4-9' ,'eeeeee'update a set info=b.info from #test a join #test1 b on DATEDIFF(dd,b.att_dt,a.att_dt)=1select * from #test drop table #test,#test1 /*id att_dt info
----------- ----------------------- ----------------------------------------------------------------------------------------------------
1 2011-04-05 00:00:00.000
2 2011-04-06 00:00:00.000 aaaaaaa
3 2011-04-07 00:00:00.000 bbbbbbbb
4 2011-04-08 00:00:00.000 ccccccc
5 2011-04-09 00:00:00.000 ddddddd(5 行受影响)*/
create table #test(id int,att_dt datetime,info nvarchar(100))
insert into #test
select 1,'2011-4-5','' union all
select 2,'2011-4-6','' union all
select 3,'2011-4-7','' union all
select 4,'2011-4-8','' union all
select 5,'2011-4-9' ,''create table #test1(id int,att_dt datetime,info nvarchar(100))
insert into #test1
select 1,'2011-4-5','aaaaaaa' union all
select 2,'2011-4-6','bbbbbbbb' union all
select 3,'2011-4-7','ccccccc' union all
select 4,'2011-4-8','ddddddd' union all
select 5,'2011-4-9' ,'eeeeee'
GO
update a
set info =b.info
from #test as a
left join #test1 as b on a.att_dt=b.att_dt+1GO
SELECT * FROM #test
/*
id att_dt info
1 2011-04-05 00:00:00.000 NULL
2 2011-04-06 00:00:00.000 aaaaaaa
3 2011-04-07 00:00:00.000 bbbbbbbb
4 2011-04-08 00:00:00.000 ccccccc
5 2011-04-09 00:00:00.000 ddddddd*/
create table #test(id int,att_dt datetime,info nvarchar(100))
insert into #test
select 1,'2011-4-5','' union all
select 2,'2011-4-6','' union all
select 3,'2011-4-7','' union all
select 4,'2011-4-8','' union all
select 5,'2011-4-9' ,''create table #test1(id int,att_dt datetime,info nvarchar(100))
insert into #test1
select 1,'2011-4-5','aaaaaaa' union all
select 2,'2011-4-6','bbbbbbbb' union all
select 3,'2011-4-7','ccccccc' union all
select 4,'2011-4-8','ddddddd' union all
select 5,'2011-4-9' ,'eeeeee'update a set a.info=b.info
from #test a
left join #test1 b on datediff(d,b.att_dt,a.att_dt)=1select * from #testid att_dt info
----------- ----------------------- ---------
1 2011-04-05 00:00:00.000 NULL
2 2011-04-06 00:00:00.000 aaaaaaa
3 2011-04-07 00:00:00.000 bbbbbbbb
4 2011-04-08 00:00:00.000 ccccccc
5 2011-04-09 00:00:00.000 ddddddd