用临时表不会,用固定表试就出问题了(我指的是两次UPDATE第二次错) create table tee (id int,v int) insert tee select 5,0 union all select 3,0 union all select 4,0 union all select 1,0 union all select 2,0 declare @i int set @i=0update b set v=@i,@i=@i+1 from (select top 1000 * from tee order by id) a,tee b where a.id=b.id select * from teeupdate b set v=@i,@i=@i+1 from (select top 1000 * from tee order by id) a,tee b where a.id=b.id select * from teedrop table tee id v ----------- ----------- 5 5 3 3 4 4 1 1 2 2(所影响的行数为 5 行) (所影响的行数为 5 行)id v ----------- ----------- 5 6 3 7 4 8 1 9 2 10(所影响的行数为 5 行)可能是我没用SP2?
对不起我说的是用表变量不会: declare @a table(id int,v int) insert @a select 5,0 union all select 3,0 union all select 4,0 union all select 1,0 union all select 2,0 declare @i int set @i=0update b set v=@i,@i=@i+1 from (select top 1000 * from @a order by id) a,@a b where a.id=b.id select * from @aupdate b set v=@i,@i=@i+1 from (select top 1000 * from @a order by id) a,@a b where a.id=b.id select * from @aid v ----------- ----------- 5 5 3 3 4 4 1 1 2 2(所影响的行数为 5 行) (所影响的行数为 5 行)id v ----------- ----------- 5 10 3 8 4 9 1 6 2 7(所影响的行数为 5 行)
我这里两个的结果一样,都是: id v ----------- ----------- 5 10 3 8 4 9 1 6 2 7
我发现以下的现象现象: 1)我将两个update中间这句: select * from tee 注释掉,结果就是: id v ----------- ----------- 5 10 3 8 4 9 1 6 2 72)若将这句select改写成: select * from tee order by id 得到的结果就是: id v ----------- ----------- 1 1 2 2 3 3 4 4 5 5id v ----------- ----------- 5 6 3 7 4 8 1 9 2 10好奇怪。不知道该怎么解释。我想应该是与SQL的查询优化有关,但想不出来是为什么这样。
to: icevi(按钮工厂) 我这里也有你说的现象,但是好像和那句select * from tee 没有关系.create table tee (id int,v int) insert tee select 5,0 union all select 3,0 union all select 4,0 union all select 1,0 union all select 2,0 declare @i int set @i=0update b set v=@i,@i=@i+1 from (select top 1000 * from tee order by id) a,tee b where a.id=b.id select * from teeupdate b set v=@i,@i=@i+1 from (select top 1000 * from tee order by id) a,tee b where a.id=b.id select * from tee结果: id v ----------- ----------- 5 5 3 3 4 4 1 1 2 2(所影响的行数为 5 行) (所影响的行数为 5 行)id v ----------- ----------- 5 10 3 8 4 9 1 6 2 7(所影响的行数为 5 行)再: declare @i int set @i=0update b set v=@i,@i=@i+1 from (select top 1000 * from tee order by id) a,tee b where a.id=b.id select * from teeupdate b set v=@i,@i=@i+1 from (select top 1000 * from tee order by id) a,tee b where a.id=b.id select * from tee结果: id v ----------- ----------- 1 1 2 2 3 3 4 4 5 5id v ----------- ----------- 5 6 3 7 4 8 1 9 2 10
N_Chow说得是,加一个CLUSTERED INDEX就不存在问题了! CREATE CLUSTERED INDEX indt ON tee(id)
create table tee (id int,v int)
insert tee select 5,0
union all select 3,0
union all select 4,0
union all select 1,0
union all select 2,0
declare @i int
set @i=0update b set v=@i,@i=@i+1 from (select top 1000 * from tee order by id) a,tee b where a.id=b.id select * from teeupdate b set v=@i,@i=@i+1 from (select top 1000 * from tee order by id) a,tee b where a.id=b.id select * from teedrop table tee
id v
----------- -----------
5 5
3 3
4 4
1 1
2 2(所影响的行数为 5 行)
(所影响的行数为 5 行)id v
----------- -----------
5 6
3 7
4 8
1 9
2 10(所影响的行数为 5 行)可能是我没用SP2?
declare @a table(id int,v int)
insert @a select 5,0
union all select 3,0
union all select 4,0
union all select 1,0
union all select 2,0
declare @i int
set @i=0update b set v=@i,@i=@i+1 from (select top 1000 * from @a order by id) a,@a b where a.id=b.id select * from @aupdate b set v=@i,@i=@i+1 from (select top 1000 * from @a order by id) a,@a b where a.id=b.id select * from @aid v
----------- -----------
5 5
3 3
4 4
1 1
2 2(所影响的行数为 5 行)
(所影响的行数为 5 行)id v
----------- -----------
5 10
3 8
4 9
1 6
2 7(所影响的行数为 5 行)
id v
----------- -----------
5 10
3 8
4 9
1 6
2 7
UPDATE一次后,再UPDATE都是错的。
那可能是我这没装SP2了,我家用的是D版的SERVER,装不上SP2
不过装了sp2!
还是sp2?
装了sp2就卸不下来了吧?有没有办法不用重新安装又卸不下!太晚了,睡觉去了!各位晚安!
睡觉吧。
1)我将两个update中间这句:
select * from tee
注释掉,结果就是:
id v
----------- -----------
5 10
3 8
4 9
1 6
2 72)若将这句select改写成:
select * from tee order by id
得到的结果就是:
id v
----------- -----------
1 1
2 2
3 3
4 4
5 5id v
----------- -----------
5 6
3 7
4 8
1 9
2 10好奇怪。不知道该怎么解释。我想应该是与SQL的查询优化有关,但想不出来是为什么这样。
我这里也有你说的现象,但是好像和那句select * from tee 没有关系.create table tee (id int,v int)
insert tee select 5,0
union all select 3,0
union all select 4,0
union all select 1,0
union all select 2,0
declare @i int
set @i=0update b set v=@i,@i=@i+1 from (select top 1000 * from tee order by id) a,tee b where a.id=b.id select * from teeupdate b set v=@i,@i=@i+1 from (select top 1000 * from tee order by id) a,tee b where a.id=b.id select * from tee结果:
id v
----------- -----------
5 5
3 3
4 4
1 1
2 2(所影响的行数为 5 行)
(所影响的行数为 5 行)id v
----------- -----------
5 10
3 8
4 9
1 6
2 7(所影响的行数为 5 行)再:
declare @i int
set @i=0update b set v=@i,@i=@i+1 from (select top 1000 * from tee order by id) a,tee b where a.id=b.id select * from teeupdate b set v=@i,@i=@i+1 from (select top 1000 * from tee order by id) a,tee b where a.id=b.id select * from tee结果:
id v
----------- -----------
1 1
2 2
3 3
4 4
5 5id v
----------- -----------
5 6
3 7
4 8
1 9
2 10
CREATE CLUSTERED INDEX indt ON tee(id)