有个小问题,请教一下!
declare @a table(id1 int,id2 int,num int,date varchar(10))
insert into @a select 11,101,145,'20060101'
insert into @a select 11,201,264,'20060101'
insert into @a select 21,101,464,'20060101'
insert into @a select 21,201,545,'20060101'update a set num=a.num+b.num from @a a,@a b
where a.id1=b.id1 and a.date=b.date and a.id2=101 and b.id2=201select * from @a
中的 update a set num=a.num+b.num from @a a,@a b
where a.id1=b.id1 and a.date=b.date and a.id2=101 and b.id2=201*
id1 id2 num date
----------- ----------- ----------- ----------
11 101 409 20060101
11 201 264 20060101
21 101 1009 20060101
21 201 545 20060101
*/
改为:update a set num=a.num+b.num from @a b,@a a
where b.id1=a.id1 and b.date=a.date and b.id2=101 and a.id2=201*
id1 id2 num date
----------- ----------- ----------- ----------
11 101 145 20060101
11 201 409 20060101
21 101 464 20060101
21 201 1009 20060101
*/结果num显示顺序不同,何解?
小弟才疏学浅,请教请教!!!!!!!!!!!
declare @a table(id1 int,id2 int,num int,date varchar(10))
insert into @a select 11,101,145,'20060101'
insert into @a select 11,201,264,'20060101'
insert into @a select 21,101,464,'20060101'
insert into @a select 21,201,545,'20060101'update a set num=a.num+b.num from @a a,@a b
where a.id1=b.id1 and a.date=b.date and a.id2=101 and b.id2=201select * from @a
中的 update a set num=a.num+b.num from @a a,@a b
where a.id1=b.id1 and a.date=b.date and a.id2=101 and b.id2=201*
id1 id2 num date
----------- ----------- ----------- ----------
11 101 409 20060101
11 201 264 20060101
21 101 1009 20060101
21 201 545 20060101
*/
改为:update a set num=a.num+b.num from @a b,@a a
where b.id1=a.id1 and b.date=a.date and b.id2=101 and a.id2=201*
id1 id2 num date
----------- ----------- ----------- ----------
11 101 145 20060101
11 201 409 20060101
21 101 464 20060101
21 201 1009 20060101
*/结果num显示顺序不同,何解?
小弟才疏学浅,请教请教!!!!!!!!!!!
where a.id1=b.id1 and a.date=b.date and a.id2=101 and b.id2=201其中update a指定修改的是a别名,因为有条件a.id2=101,所以修改id2=101的记录id2=201的不变同样
update a set num=a.num+b.num from @a b,@a a
where b.id1=a.id1 and b.date=a.date and b.id2=101 and a.id2=201
其中update a指定修改的是a别名,因为有条件a.id2=201,所以修改id2=201的记录id2=101的不变