现在我有一张表A,有字段a int ,b nvarchar, c smalldatetime ,d smalldatetime
现在我要做得是把 d = c
有个前提条件是b相等,而且a = a + 1
例如:
a b c d
---------------------------------
1 a 20080101
2 a 20080102
3 a 20080103
4 b 20080102
5 c 20080103
结果应该为:
a b c d
---------------------------------
1 a 20080101 20080102
2 a 20080102 20080103
3 a 20080103 null
4 b 20080102 null
5 c 20080103 null
现在我要做得是把 d = c
有个前提条件是b相等,而且a = a + 1
例如:
a b c d
---------------------------------
1 a 20080101
2 a 20080102
3 a 20080103
4 b 20080102
5 c 20080103
结果应该为:
a b c d
---------------------------------
1 a 20080101 20080102
2 a 20080102 20080103
3 a 20080103 null
4 b 20080102 null
5 c 20080103 null
update tb
set d=a.c
from tb a
where a.b=tb.b and a.a=tb.a+1
insert into tb values('1' , 'a' , '20080101')
insert into tb values('2' , 'a' , '20080102')
insert into tb values('3' , 'a' , '20080103')
insert into tb values('4' , 'b' , '20080102')
insert into tb values('5' , 'c' , '20080103')
goselect * , d = (select c from tb where b = t.b and a = t.a + 1 ) from tb tdrop table tb/*
a b c d
---------- ---------- ---------- ----------
1 a 20080101 20080102
2 a 20080102 20080103
3 a 20080103 NULL
4 b 20080102 NULL
5 c 20080103 NULL(所影响的行数为 5 行)
*/
d=a2.c
from a a1,a a2
where a2.a=a1.a+1
and a2.b=a1.b
insert into tb values('1' , 'a' , '20080101',null)
insert into tb values('2' , 'a' , '20080102',null)
insert into tb values('3' , 'a' , '20080103',null)
insert into tb values('4' , 'b' , '20080102',null)
insert into tb values('5' , 'c' , '20080103',null)
go
--查询
select a , b , c, d = (select c from tb where b = t.b and a = t.a + 1 ) from tb t--更新
update tb set d = (select c from tb where b = t.b and a = t.a + 1 ) from tb tdrop table tb/*
a b c d
---------- ---------- ---------- ----------
1 a 20080101 20080102
2 a 20080102 20080103
3 a 20080103 NULL
4 b 20080102 NULL
5 c 20080103 NULL(所影响的行数为 5 行)
*/
赋给d
update tb set d = (select c from tb where b = t.b and a = t.a + 1 ) from tb t加了不连续a相同b后再连续a相同b,测试通过!呵呵
很强大