- SQL code
--测试数据CREATE table t1 (Id_1 int primary key identity,ch_1 varchar(20))insert into t1select '0123' unionselect '1124' unionselect '1125' unionselect '1126' unionselect '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 t2select 'SP1204001','1124' unionselect 'SP1204002','1126' unionselect 'SP1204003','1125' unionselect 'SP1204004','2012-1127'select * from t2left 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(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')
------解决方案--------------------
- SQL code
--推理妞，连接查询的时候，字段对应的不对CREATE table t1 (Id_1 int primary key identity,ch_1 varchar(20))insert into t1select '0123' unionselect '1124' unionselect '1125' unionselect '1126' unionselect '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 t2select 'SP1204001','1124' unionselect 'SP1204002','1126' unionselect 'SP1204003','1125' unionselect 'SP1204004','2012-1127'select * from t2left join t1 on t2.ch_2=t1.ch_1/*Id_2 ch_1 ch_2 Id_1 ch_1----------- -------------------- -------------------- ----------- --------------------1 SP1204001 1124 2 11242 SP1204002 1126 4 11263 SP1204003 1125 3 11254 SP1204004 2012-1127 5 2012-11274 SP1204004 2012-1127 6 2012-1127*/
------解决方案--------------------
原来的是连接条件是int 字段对应 varchar 类型字段了。
如果不想让它报错也是可以的。
- SQL code
select * from t2left join t1 on t2.ch_2=LTRIM(t1.id_1)/*Id_2 ch_1 ch_2 Id_1 ch_1----------- -------------------- -------------------- ----------- --------------------1 SP1204001 1124 NULL NULL2 SP1204002 1126 NULL NULL3 SP1204003 1125 NULL NULL4 SP1204004 2012-1127 NULL NULL*/
------解决方案--------------------