如:
select * from [tabale] where id in ('100','101','102') ----OK
select * from [tabale] where id in (100,101,102) ----OK
若:
select * from [tabale] where id in ('100,101,102') ----No
在将 varchar 值 '100,101,102' 转换成数据类型 int 时失败。select * from [tabale] where id in (select top 1 f_id * from [table2]) ----No
(f_id字段内容为 100,101,102 字段内型是varchar)select * from [tabale] where cast(id as varchar) in (select top 1 f_id * from [table2]) -----No
Why ? how to do ?
select * from [tabale] where id in ('100','101','102') ----OK
select * from [tabale] where id in (100,101,102) ----OK
若:
select * from [tabale] where id in ('100,101,102') ----No
在将 varchar 值 '100,101,102' 转换成数据类型 int 时失败。select * from [tabale] where id in (select top 1 f_id * from [table2]) ----No
(f_id字段内容为 100,101,102 字段内型是varchar)select * from [tabale] where cast(id as varchar) in (select top 1 f_id * from [table2]) -----No
Why ? how to do ?
select top 1 f_id * from [table2]
in 语句只能指定一个列
in ('100,101,102') 是必须这个字符串中含有'100,101,102' 这个整体
--'100,101,102'是一个字符串, 因为id字段是整型, 所以在条件判断时会进行转换,但转换失败.正确的:
select * from [tabale] where id in ('100', '101', '102')
--#2. select * from [tabale] where id in (select top 1 f_id * from [table2])
--IN 后面结果集必须只有一列,如果有TOP 1,也可以用=条件.正确的:
select * from [tabale] where id = (select top 1 f_id from [table2])
--#3. select * from [tabale] where cast(id as varchar) in (select top 1 f_id * from [table2])
--参考#2.如果要自己进行数据转换,应该如下.正确的:
select * from [tabale] where id in (select top 1 cast(f_id as varchar(20)) from [table2])
select * from [tabale] where cast(id as varchar(20)) in (select top 1 f_id from [table2])