select (select top 1 from list where id in 问题

[powbcom]

SELECT Tb.ID,
(select top 1 from list where id in (tb.imglist)) as img,

FROM @indextable t, Tb where Tb.id =t.nid
and t.id between @PageLowerBound and @PageUpperBound order by t.id

问题是
(select top 1 from list where id in (tb.imglist)) as img 取了一个记录

tb.imglist 为varchar 类型,存储数据为:1001,1002

要从list表中取出一条记录,请大家帮帮忙

主要错误是系统会 tb.imglist 转换成int 而数据是1001,1002 不可能转换成功,有人遇到吗?

[krpol2005]

把where 换成
exists
(
select * from tb
where id=imglist)

[krpol2005]

exists
(
select * from tb
where imglist=id)

[pbsql]

(select top 1 id from list where id in (tb.imglist)) as img
换成
(select top 1 id from list where tb.imglist like '%,'+id+',%') as img

[子陌红尘]

SELECT
Tb.ID,
(select top 1 from list where charindex(','+rtrim(id)+',',','+tb.imglist+',')>0) as img
FROM
@indextable t, Tb
where
Tb.id =t.nid
and
t.id between @PageLowerBound and @PageUpperBound
order
by t.id

[filebat]

while charindex(convert(varchar(10), id)), tb.imglist)<>0
这样可以吗？

[zlp321002]

--对啊!风云的也可以啊.
--也可以这样,我习惯这样,效率高


SELECT Tb.ID,
(select top 1 from list where charindex(','+cast(id as varchar)+',',','+tb.imglist+',')>0 ) as img,

FROM @indextable t, Tb where Tb.id =t.nid
and t.id between @PageLowerBound and @PageUpperBound order by t.id

[powbcom]

谢谢大家的支持,我逐一试过,取出的值都是 null

[powbcom]

另外,使用like不太好,因为估计到以所肯定有重复的记录,比如:10001 和100011,所以like不行

[pbsql]

like是可以的，自己试

[powbcom]

like 不行, like '%,'+id+',%') 只能取中间记录,两头的就取不到,
如果不加两头的,号,像10001 和100011,就变成了等同了

[pbsql]

这样：
where ','+tb.imglist+',' like '%,'+id+',%'