兩個結構,資料一樣的表,執行一樣的sql,為甚麼效果不一樣

[yone]

drop table tmp_profit1
go
drop table tmp2
go

create table tmp_profit1(
id int not null,
subject varchar(30) not null ,
cname varchar(50) null,
ename varchar(50) null,
leaf tinyint null,
primary key clustered(subject)
)
go

INSERT INTO tmp_profit1 VALUES(1,'4','[營業收入類]','[Operating revenue]',0)
INSERT INTO tmp_profit1 VALUES(2,'41','營業收入','Operating revenue',0)
INSERT INTO tmp_profit1 VALUES(3,'4101','銷貨收入','Sales Revenue',1)
INSERT INTO tmp_profit1 VALUES(4,'4102','減:銷貨退回','減:Sales Returns',1)
INSERT INTO tmp_profit1 VALUES(5,'4104','減:銷貨折讓','減:Sales Allowances',1)
INSERT INTO tmp_profit1 VALUES(6,'4105','test of 4105','test of 4105',1)
INSERT INTO tmp_profit1 VALUES(7,'42','營業收入','Operating revenue',0)
go

select * into tmp2 from tmp_profit1

declare @space char(3)
select @space =' '
update a
set cname = @space + a.cname ,ename = @space + a.ename
from tmp2 a, tmp2 b
where substring(a.subject,1,len(b.subject)) = b.subject and len(b.subject) < len(a.subject)
and b.leaf =0
go

declare @space char(3)
select @space =' '
update a
set cname = @space + a.cname ,ename = @space + a.ename
from tmp_profit1 a, tmp_profit1 b
where substring(a.subject,1,len(b.subject)) = b.subject and len(b.subject) < len(a.subject)
and b.leaf =0
go

select * from tmp_profit1
select * from tmp2

go

上面會計科目的一部分,每一級子科目前面加3空格,多級多加.
tmp2是tmp_profit1直接導出的表.執行的sql也一樣,為甚麼結果不一樣.
tmp2所有級別子科目都只加了一次,3個空個.
而tmp_profit1是正確的.

[eru]

我这里执行一模一样

[eru]

(1 row(s) affected)


(1 row(s) affected)


(1 row(s) affected)


(1 row(s) affected)


(1 row(s) affected)


(1 row(s) affected)


(1 row(s) affected)


(7 row(s) affected)


(6 row(s) affected)


(6 row(s) affected)

id subject cname ename leaf
----------- ------------------------------ -------------------------------------------------- -------------------------------------------------- ----
1 4 [營業收入類] [Operating revenue] 0
2 41 營業收入 Operating revenue 0
3 4101 銷貨收入 Sales Revenue 1
4 4102 減:銷貨退回 減:Sales Returns 1
5 4104 減:銷貨折讓 減:Sales Allowances 1
6 4105 test of 4105 test of 4105 1
7 42 營業收入 Operating revenue 0

(7 row(s) affected)

id subject cname ename leaf
----------- ------------------------------ -------------------------------------------------- -------------------------------------------------- ----
1 4 [營業收入類] [Operating revenue] 0
2 41 營業收入 Operating revenue 0
3 4101 銷貨收入 Sales Revenue 1
4 4102 減:銷貨退回 減:Sales Returns 1
5 4104 減:銷貨折讓 減:Sales Allowances 1
6 4105 test of 4105 test of 4105 1
7 42 營業收入 Operating revenue 0

(7 row(s) affected)

[txlicenhe]

有点意思：
declare @space char(3)
select @space =' '
update a
set cname = @space + a.cname ,ename = @space + a.ename
from tmp2 a, tmp2 b
where substring(a.subject,1,len(b.subject)) = b.subject and len(b.subject) < len(a.subject)
and b.leaf =0
go
这一句所影响的行为6行

declare @space char(3)
select @space =' '
update a
set cname = @space + a.cname ,ename = @space + a.ename
from tmp_profit1 a, tmp_profit1 b
where substring(a.subject,1,len(b.subject)) = b.subject and len(b.subject) < len(a.subject)
and b.leaf =0
go
这一句所影响的行为10行

但是
Select * from tmp2 a, tmp2 b
where substring(a.subject,1,len(b.subject)) = b.subject and len(b.subject) < len(a.subject)
and b.leaf =0
和
select * from tmp_profit1 a, tmp_profit1 b
where substring(a.subject,1,len(b.subject)) = b.subject and len(b.subject) < len(a.subject)
and b.leaf =0

的执行结果是一样的。

[eru]

太怪异了吧？
你select一下tmp_profit1看看是几条记录
总共就7条数据怎么会影响10行呢？
我这里都是6行

[killxtt]

我刚才又看了一下，
select * into tmp2 from tmp_profit1
这句产生的tmp2表是没有primary key的，但是原来的tmp_profit1是有的，primary key是subject。
不知道和这个又没有关系，高手们对primary key了解的比较透彻，分析一下看看呢

[yone]

頂,哪位高手來分析一下

[yone]

的確有沒有index是一個問題,現在還發現就是tmp_profit1這個表在不同的server上也有兩種結果.
看來這個sql是太依賴環境了,要重寫了.

[yone]

有的記錄會被UPDATE 2次,這個是可以理解的,但是有時僅是一次就奇怪了.
頂

[Benimarunikado]

真的是匪夷所思——
我查看了一下两个过程的条件是一样的啊--
select *
from tmp2 a, tmp2 b
where substring(a.subject,1,len(b.subject)) = b.subject and len(b.subject) < len(a.subject)
and b.leaf =0
select *
from tmp_profit1 a, tmp_profit1 b
where substring(a.subject,1,len(b.subject)) = b.subject and len(b.subject) < len(a.subject)
and b.leaf =0
结果都是10行啊～～～
难道是bug～～～
郁闷ing

[yone]

頂,

[killxtt]

确实是阿，怪异，顶