ts_users 表的 TS_id 类型为INT
ts_selections 表的 Ts_userid,ts_id都为INT
ts_MSSELECTION表的 slaveselections 为Text,内容为(逗号间隔开的INT数串)
如:913,343,1,234,245,3434下面这样的SQL 没有 结果
select * from ts_users where cast(ts_id as varchar(200)) in
(select ts_userid from ts_selections where ts_fldid=107 and cast(ts_id as varchar(200)) in
(SELECT CAST( ts_slaveselections AS varchar(200)) as aaa FROM TS_MSSELECTION
WHERE ts_masterid=144 and ts_workflowid=15 and ts_slaveid=107))
可写成这个样有结果,真是搞不懂怎么回事了 先得到数字串列表:(SELECT CAST( ts_slaveselections AS varchar(200)) as aaa FROM TS_MSSELECTION
WHERE ts_masterid=144 and ts_workflowid=15 and ts_slaveid=107))结果是:
1324,3424,234,2,34,23
354,454,3452,342,4334
...
....再执行这样的SQL就会有结果:
select * from ts_users where cast(ts_id as varchar(200)) in
(select ts_userid from ts_selections where ts_fldid=107 and cast(ts_id as varchar(200)) in (354,454,3452,342,4334))这是为什么?
ts_selections 表的 Ts_userid,ts_id都为INT
ts_MSSELECTION表的 slaveselections 为Text,内容为(逗号间隔开的INT数串)
如:913,343,1,234,245,3434下面这样的SQL 没有 结果
select * from ts_users where cast(ts_id as varchar(200)) in
(select ts_userid from ts_selections where ts_fldid=107 and cast(ts_id as varchar(200)) in
(SELECT CAST( ts_slaveselections AS varchar(200)) as aaa FROM TS_MSSELECTION
WHERE ts_masterid=144 and ts_workflowid=15 and ts_slaveid=107))
可写成这个样有结果,真是搞不懂怎么回事了 先得到数字串列表:(SELECT CAST( ts_slaveselections AS varchar(200)) as aaa FROM TS_MSSELECTION
WHERE ts_masterid=144 and ts_workflowid=15 and ts_slaveid=107))结果是:
1324,3424,234,2,34,23
354,454,3452,342,4334
...
....再执行这样的SQL就会有结果:
select * from ts_users where cast(ts_id as varchar(200)) in
(select ts_userid from ts_selections where ts_fldid=107 and cast(ts_id as varchar(200)) in (354,454,3452,342,4334))这是为什么?
go
insert a
select 1,'AA' union all
select 2,'BB' union all
select 3,'CC'select * from a where a.id in (1,2,3)--第一种方法
declare @s varchar(1000),@s2 varchar(1000)
set @s2 = '1,2,3'
set @s='select * from a where a.id in (' + @s2 + ')'
select @s
exec (@s)
select * from a where a.id in ('1','2','3')--第二种方法
select * from a where charindex(','+id+',',(select ','+name+',' from b where id='3'))>0
drop table a,b
而你用 in(select ...), 则 select 的每个列的值是表达式列表中的一项, 而不是列中每个逗分隔的拆分后的值是表达式列表中的一项
代码不全吧?
declare @s varchar(1000),@值列表 varchar(1000)
set @值列表= '''aa'',''bb'',''cc'''
set @s='select * from 表名 where 字段 in (' + @值列表 + ')'
print @s
exec (@s)
--第二种方法:使用charindex
declare @值列表 varchar(1000)
set @值列表='aa,bb,cc'
select * from 表名 where charindex(',' + 字段 + ',',',' + @值列表 + ',') > 0