--测试数据
CREATE table t1 (Id_1 int primary key identity,ch_1 varchar(20))
insert into t1
select '0123' union
select '1124' union
select '1125' union
select '1126' union
select '2012-1127'
insert into t1 values ('2012-1127')
CREATE table t2 (Id_2 int primary key identity,ch_1 varchar(20),ch_2 varchar(20))
insert into t2
select 'SP1204001','1124' union
select 'SP1204002','1126' union
select 'SP1204003','1125' union
select 'SP1204004','2012-1127'
select * from t2
left join t1 on t2.ch_2=t1.id_1--结果服务器: 消息 245,级别 16,状态 1,行 1
将 varchar 值 '2012-1127' 转换为数据类型为 int 的列时发生语法错误。
CREATE table t1 (Id_1 int primary key identity,ch_1 varchar(20))
insert into t1
select '0123' union
select '1124' union
select '1125' union
select '1126' union
select '2012-1127'
insert into t1 values ('2012-1127')
CREATE table t2 (Id_2 int primary key identity,ch_1 varchar(20),ch_2 varchar(20))
insert into t2
select 'SP1204001','1124' union
select 'SP1204002','1126' union
select 'SP1204003','1125' union
select 'SP1204004','2012-1127'
select * from t2
left join t1 on t2.ch_2=t1.id_1--结果服务器: 消息 245,级别 16,状态 1,行 1
将 varchar 值 '2012-1127' 转换为数据类型为 int 的列时发生语法错误。
insert into t1(ch_1) --插入的列名要带上的
select '0123' union
select '1124' union
select '1125' union
select '1126' union
select '2012-1127'insert into t1(ch_1) --插入的列名要带上的
values ('2012-1127')
select * from t2
left join t1 on t2.ch_2=t1.ch_1
--推理妞,连接查询的时候,字段对应的不对
CREATE table t1 (Id_1 int primary key identity,ch_1 varchar(20))
insert into t1
select '0123' union
select '1124' union
select '1125' union
select '1126' union
select '2012-1127'
insert into t1 values ('2012-1127')CREATE table t2 (Id_2 int primary key identity,ch_1 varchar(20),ch_2 varchar(20))
insert into t2
select 'SP1204001','1124' union
select 'SP1204002','1126' union
select 'SP1204003','1125' union
select 'SP1204004','2012-1127'select * from t2
left join t1 on t2.ch_2=t1.ch_1
/*
Id_2 ch_1 ch_2 Id_1 ch_1
----------- -------------------- -------------------- ----------- --------------------
1 SP1204001 1124 2 1124
2 SP1204002 1126 4 1126
3 SP1204003 1125 3 1125
4 SP1204004 2012-1127 5 2012-1127
4 SP1204004 2012-1127 6 2012-1127
*/
如果不想让它报错也是可以的。select * from t2
left join t1 on t2.ch_2=LTRIM(t1.id_1)
/*
Id_2 ch_1 ch_2 Id_1 ch_1
----------- -------------------- -------------------- ----------- --------------------
1 SP1204001 1124 NULL NULL
2 SP1204002 1126 NULL NULL
3 SP1204003 1125 NULL NULL
4 SP1204004 2012-1127 NULL NULL
*/
看到叶子大哥的七楼了
CSDN又抽了