现有approve和projectReg两张表,没有物理外键。
approve: businessCode, type
projectReg: projectId
select *
from projectReg a LEFT JOIN approve b
on a.projectRegId = b.businessCode
select *
from approve a LEFT JOIN projectReg b
on a.businessCode=b.projectRegId
都会报错:[Err] 22018 - [SQL Server]在将 varchar 值 '3ad045bd10c1482d962ec944bca21056' 转换成数据类型 int 时失败。
select *
from projectReg a LEFT JOIN approve b
on a.projectRegId = b.businessCode and b.type=5
不报错
select *
from projectReg a LEFT JOIN approve b
on a.projectRegId = b.businessCode
-- and b.type=5
where b.type=5
将过滤被连接的表的条件放到where中,同样不报错
select *
from approve a LEFT JOIN projectReg b
on a.businessCode=b.projectRegId and a.type=5
报错:[Err] 22018 - [SQL Server]在将 varchar 值 '3ad045bd10c1482d962ec944bca21056' 转换成数据类型 int 时失败。
select *
from approve a LEFT JOIN projectReg b
on a.businessCode=b.projectRegId
-- and a.type=5
where a.type=5
这样也不报错
更有意思的是:
select *
from approve a LEFT JOIN projectReg b
on a.businessCode=b.projectRegId
and a.type=5
where a.type=5
居然也不报错,很无奈啊!!!!
总结:
仅仅尝试了sql server
1.网上关于on和where条件是先生成临时表再用where去过滤的情况,在sql server中是不成立的。
2.关于对主表的条件过滤,应该放到where中