t1和t2表的主键都是No和SubNo
create table t1(No char(10),SubNo char(2),pNo char(10),pSubNo char(2),Name VARCHAR(10))
create table t2(No char(10),SubNo char(2),Name VARCHAR(10))
t1
10000000001,01,20000000001,01,a1
10000000001,02,'','',a2
10000000002,01,'','',b1
10000000001,02,'','',b1t2
20000000001,01,c1
20000000001,02,c2select *
from t1
left outer join t2
on t1.pNo = t2.No
and t1.pSubNo = t2.SubNo
where cast(t1.pNo as decimal)<30000000001select *
from t1
left outer join t2
on t1.pNo = t2.No
and t1.pSubNo = t2.SubNo
where cast(t2.No as decimal)<30000000001
第一个sql会出错,求教为什么pNo为空白的时候会出错,但是用第二张表的主键做条件却不出错
create table t1(No char(10),SubNo char(2),pNo char(10),pSubNo char(2),Name VARCHAR(10))
create table t2(No char(10),SubNo char(2),Name VARCHAR(10))
t1
10000000001,01,20000000001,01,a1
10000000001,02,'','',a2
10000000002,01,'','',b1
10000000001,02,'','',b1t2
20000000001,01,c1
20000000001,02,c2select *
from t1
left outer join t2
on t1.pNo = t2.No
and t1.pSubNo = t2.SubNo
where cast(t1.pNo as decimal)<30000000001select *
from t1
left outer join t2
on t1.pNo = t2.No
and t1.pSubNo = t2.SubNo
where cast(t2.No as decimal)<30000000001
第一个sql会出错,求教为什么pNo为空白的时候会出错,但是用第二张表的主键做条件却不出错
2、从语法上:
SELECT CAST('' AS decimal)消息 8114,级别 16,状态 5,第 1 行
从数据类型 varchar 转换为 numeric 时出错。证明直接这样转是不行的。