--1.请大家分别在2000与2005中运行,看看#t1表中的fQty的值是多少
--2.为什么会有这种差别呢create table #t1(fCode varchar(10),fQty int)
create table #t2(fCode varchar(10),fQty int)insert into #t1(fCode,fQty)
select 'A',0
insert into #t2(fCode,fQty)
select 'A',5 union all
select 'A',4 union all
select 'A',8 union all
select 'A',1 union all
select 'A',3update a set a.fQty=b.fQty
from #t1 a join #t2 b on a.fCode=b.fCodeselect * from #t1
create table #t1(fCode varchar(10),fQty int)
create table #t2(fCode varchar(10),fQty int)insert into #t1(fCode,fQty)
select 'A',0
insert into #t2(fCode,fQty)
select 'A',5 union all
select 'A',4 union all
select 'A',8 union all
select 'A',1 union all
select 'A',3update a set a.fQty=b.fQty
from #t1 a join #t2 b on a.fCode=b.fCodeselect * from #t1
/*
fCode fQty
---------- -----------
A 5
*/
drop table #t1
drop table #t2
/*
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
*/
create table #t1(fCode varchar(10),fQty int)
create table #t2(fCode varchar(10),fQty int)insert into #t1(fCode,fQty)
select 'A',0
insert into #t2(fCode,fQty)
select 'A',5 union all
select 'A',4 union all
select 'A',8 union all
select 'A',1 union all
select 'A',3update a set a.fQty=b.fQty
from #t1 a join #t2 b on a.fCode=b.fCodeselect * from #t1
select @@versiondrop table #t1,#t2
/***********fCode fQty
---------- -----------
A 5(1 行受影响)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
(1 行受影响)
fCode fQty
---------- -----------
A 3--分析::a的fCode和b的五个fCode都匹配,
--但更新时是按顺序取最后一个,类似select @a=name from tb,结果@a肯定是tb表的最后一个name
SQL SERVER 2000 我没有环境试不了。
但是建议杰西给两个表都加上主键再更新。就算是fCode列是一对多的,再多加个列,设置主键。
估计更新的时候可能2000 和 2005就一样了...
create table #t2(id int identity(1,1) primary key ,fCode varchar(10),fQty int)insert into #t1(fCode,fQty)
select 'A',0
insert into #t2(fCode,fQty)
select 'A',5 union all
select 'A',4 union all
select 'A',8 union all
select 'A',1 union all
select 'A',3update a set a.fQty=b.fQty
from #t1 a join #t2 b on a.fCode=b.fCodeselect * from #t1
/*
id fCode fQty
----------- ---------- -----------
1 A 5
*/
drop table #t1
drop table #t2
/*
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
*/豆子试试这样在2000中的结果...
--直接select * from #t2
create table #t1(fCode varchar(10),fQty int)
create table #t2(fCode varchar(10),fQty int)insert into #t1(fCode,fQty)
select 'A',0
insert into #t2(fCode,fQty)
select 'A',5 union all
select 'A',4 union all
select 'A',8 union all
select 'A',1 union all
select 'A',3select * from #t2
/*
fCode fQty
---------- -----------
A 5
A 4
A 8
A 1
A 3
*/
fCode fQty
---------- -----------
A 5(1 行受影响)
--05版sql。楼上大哥们试试2000版的.....
id fCode fQty
----------- ---------- -----------
1 A 3
declare @i int
set @i=0update a set a.fQty=b.fQty,@i=@i+1
from #t1 a join #t2 b on a.fCode=b.fCodeselect @i--得@i=1,而不是想象中的5,这又说明了什么?
所以无论你把,@i=@i+1写在前面和后果,效果是一样的
declare @t table (id int)
insert into @t
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5declare @i int
set @i=0
update @t
set id=6,@i=@i+1
--where id=3
select @i
/*
5
*/
--把where id=3加上,结果就是1了
--更新一行,@i增加1
declare @t table (id int,col int)
insert into @t
select 1,5 union all
select 2,6 union all
select 3,7 union all
select 4,8declare @i int ;set @i=5
update @t
set col=id+@i,@i=@i+1
select * from @t
/*
id col
----------- -----------
1 7
2 9
3 11
4 13
*/例子一看就明白了...
其实这个update只执行了一次,所以变量也只会加一次
IF OBJECT_ID('TEMPDB..#T1') IS NOT NULL DROP TABLE #T1
IF OBJECT_ID('TEMPDB..#T2') IS NOT NULL DROP TABLE #T2
GO
create table #t1(fCode varchar(10),fQty int)
create table #t2(fCode varchar(10),fQty int)insert into #t1(fCode,fQty)
select 'A',0
insert into #t2(fCode,fQty)
select 'A',5 union all
select 'A',4 union all
select 'A',8 union all
select 'A',1 union all
select 'A',3
GO
SET SHOWPLAN_TEXT ON
GO
update a set a.fQty=b.fQty
from #t1 a join #t2 b on a.fCode=b.fCode
GO
SET SHOWPLAN_TEXT OFF
GO
/*
|--Table Update(OBJECT:([tempdb].[dbo].[#t1] AS [a]), SET:([tempdb].[dbo].[#t1].[fQty] as [a].[fQty] = [tempdb].[dbo].[#t2].[fQty] as [b].[fQty]))
|--Top(ROWCOUNT est 0)
|--Stream Aggregate(GROUP BY:([Bmk1000]) DEFINE:([b].[fQty]=ANY([tempdb].[dbo].[#t2].[fQty] as [b].[fQty])))
|--Nested Loops(Inner Join, WHERE:([tempdb].[dbo].[#t2].[fCode] as [b].[fCode]=[tempdb].[dbo].[#t1].[fCode] as [a].[fCode]))
|--Table Scan(OBJECT:([tempdb].[dbo].[#t1] AS [a]))
|--Table Scan(OBJECT:([tempdb].[dbo].[#t2] AS [b]))
*/
从执行计划里可以看到,最后是用TOP取到的更新源变量,而TOP排序的依据是流聚合里作为分组依据的Bmk1000,这个列是在上一步的嵌套循环里生成的,这个排序应该是由两个基表的读取顺序生成的,众所周知堆表的输出是无序的,所以不同版本、不同数据量、不同环境、某些操作都有可能使输出发生变更。
當以下條件滿足時,分組取第1條Group by a.fCode
[b].[fQty]=ANY([tempdb].[dbo].[#t2].[fQty] as [b].[fQty])
UPDATE T1 SET C1=...,C2=...@I1=...,@I2=...
这样的更新,每行都是先做更新目标为变量的更新,再做更新目标为列的更新,两部分的内部顺序都是从左到右。上面那条语句的顺序是@T1,@T2,C1,C2。如果这种更新是做累计的运算,排序就无意义,如果是有顺序的运算(最典型的是根据日期算某个公式的结果),没有ORDER BY的话说不定哪天就出问题了。我当时找到的唯一靠谱的解决方案就是用CTE或临时表生成排序辅助列,按辅助列排序,然后逐行更新。
------------------------
fCode fQty
A 3-------------------------------
sql2005
-------
fCode fQty
A 5
这条语句是有5条记录,按理
update a set a.fQty=b.fQty,@i=@i+1
from #t1 a join #t2 b on a.fCode=b.fCode
应该执行5次,也就是说,@i=5,而实际@i=1,所以一时理解不透
还有就是2000与2005出现的结果不一样,那到底哪一个才是标准?
这条语句是有5条记录,按理
update a set a.fQty=b.fQty,@i=@i+1
from #t1 a join #t2 b on a.fCode=b.fCode
应该执行5次,也就是说,@i=5,而实际@i=1,所以一时理解不透
还有就是2000与2005出现的结果不一样,那到底哪一个才是标准?两个版本的执行计划不一样,没有标准。UPDATE里加变量更新本身就是在官方文档里没有的,是大家自己研究出的用法。
在更新之前会按条件on a.fCode=b.fCode一直向下找,
但找的过程中和update语句中的a.fQty=b.fQty,@i=@i+1是没关系的,
只和on a.fCode=b.fCode有关,找到后再执行update a set a.fQty=b.fQty,@i=@i+1
其实你只要记录记住,update表中记录中,每行记录只会被更新一次就可以了,
连接条件只是按条件用来查找所匹配数据,这样说应该明白了吧,
from #t1 a join #t2 b on a.fCode=b.fCode COLLATE Chinese_Taiwan_Stroke_CI_AS
2000和2005 update 出来有差异确实有点意思。
同样的语句 我用Sybase(12.5.4)试了下 结果是:
A 5oracle应该是不支持那种update的写法的。这个结果难道和临时表在数据库中数据的存储顺序有关?
理论上来说 这种更新语句应该要判定为错误的更新的,毕竟一对多的更新在实际的需求中是不允许的。
不同版本的数据库环境的查询分析计划是不同的,而具体这个语句得到那一个结果也应该是由这个确定的分析计划所确定的,但是在相同的版本下面得到的计划肯定是相同的
,而这个更新语句得到的结果也是确定的。
SQL的开发是不断的 未来不同的版本对这个语句所产生的结果也是不确定的。而确定这个结果的机制应该完全取决于这个版本的分析计划本身的设计。
A 5
------------------
不是,随机的,哪儿有空间插哪儿。你观察到的是插入先后顺序是因为你的表太小而数据库空间又足够大。
参考inside sql server 2005 storage engine
反过来说如果有建索引2000与2005会得到相同结果?
--------------------
是的,如果两表只有在fCode上一个聚集索引,必然得到相同的结果如果有多个索引,使用索引提示强制两表都使用fCode的索引,必然的到相同的结果
CREATE CLUSTERED INDEX [IX_#t1] ON [dbo].[#t1]([fCode]) ON [PRIMARY]create table #t2(fCode varchar(10),fQty int)
CREATE CLUSTERED INDEX [IX_#t2] ON [dbo].[#t2]([fCode]) ON [PRIMARY]insert into #t1(fCode,fQty)
select 'A',0
insert into #t2(fCode,fQty)
select 'A',5 union all
select 'A',4 union all
select 'A',8 union all
select 'A',1 union all
select 'A',3update a set a.fQty=b.fQty
from #t1 a join #t2 b on a.fCode=b.fCodeselect * from #t1drop table #t1,#t2
在2000下的结果:
fCode fQty
---------- -----------
A 3
在2005下的结果:
fCode fQty
---------- -----------
A 5
很显然mengmou,其这不用试就可想到结果,对fCode做聚集索引对我所列举的数据不会有影响,因为fCode就只一个值,那就是A, 有没有索引都是一个结果
create table #t1(fCode varchar(10),fQty int)
CREATE CLUSTERED INDEX [IX_#t1] ON [dbo].[#t1]([fCode],[fQty]) ON [PRIMARY]create table #t2(fCode varchar(10),fQty int)
CREATE CLUSTERED INDEX [IX_#t2] ON [dbo].[#t2]([fCode],[fQty]) ON [PRIMARY]insert into #t1(fCode,fQty)
select 'A',0
insert into #t2(fCode,fQty)
select 'A',5 union all
select 'A',4 union all
select 'A',8 union all
select 'A',1 union all
select 'A',3update a set a.fQty=b.fQty
from #t1 a join #t2 b on a.fCode=b.fCodeselect * from #t1drop table #t1,#t2如果是这样,mengmou的结论倒是正确的,这倒让我奇怪了,不知有哪位高手可以解释一下么?在2000与2005下都是:fCode fQty
---------- -----------
A 1
--CREATE CLUSTERED INDEX [IX_#t1] ON [dbo].[#t1]([fCode],[fQty]) ON [PRIMARY]
把上面#t1这个聚集索引去掉,只保留#t2聚集索引的,更新后的值就不是1而是8(sql2000),
都建立了聚集索引后,两个表都有一个索引值,更新时似乎根据第一个匹配的索引值,
再找到其对应的数据项
-----------------
好吧,我承认我看错了,没看见fcode都是一样的。总之问题的关键就在于1) 两表中的记录是按什么顺序存储的
2) update语句是按什么顺序访问两个表中的记录的
A 3
A 3
A 3
例子中,b.fQty=5的值,是第一个被插入的,所以a表的值更新成5.
你有t1 和 t2两本字典
没有索引的时候 当然都不存在索引页 只有数据页
t2里面的数据当然在数据页里面当然是有顺序的
同样的语句去查询字典 这个动作当然是一致的 就像你把字典从第一页翻到最后一页一样。
而查询出来结果数据的顺序跟这些数据存于字典的顺序应该是一致的。
建立了索引之后 字典里面就有了索引页 ,查询的时候走索引的话 查询出来的顺序。就是按照检索索引的顺序去查询数据的。不同版本的不同类型的数据库系统就相当于不同的人 每个人查询字典的方式可能都是不同的
有的从第一页开始 有的从最后开始 这个主要看那样数据库设计者是怎么弄的了。
以上仅仅是猜测 希望对楼主有帮助。