表A数据
ID LONGNAME
1 1,2,3,4
2 2,3,4
3 2,3表B数据
CODE NAME
1 EN
2 TG
3 FG
4 SI
SQL查询语句
select code,name from B where code in (select LONGNAME from A where ID = 2)想要结果
TG
FG
SI
为什么 查询出来的结果为空呢 ?请各位指教
ID LONGNAME
1 1,2,3,4
2 2,3,4
3 2,3表B数据
CODE NAME
1 EN
2 TG
3 FG
4 SI
SQL查询语句
select code,name from B where code in (select LONGNAME from A where ID = 2)想要结果
TG
FG
SI
为什么 查询出来的结果为空呢 ?请各位指教
select code,name from B where
(select count(1) from A where charindex(cast(b.code as varchar(10)),ID)>0)>=1
(select count(1) from A where id=2 and charindex(cast(b.code as varchar(10)),LONGNAME)>0)>=1
select code,name from B where code in (select LONGNAME from A where ID = 2)
其中
select LONGNAME from A where ID = 2 得到是 一个对象组成的集合, 这个对象由2,3,4数组--集合组成
code 是个一个元素,是1 或者 2 或者 3 或者4
1 in{{2,3,4}} 结果 肯定是空
select code,name from B where code in (..) 是要从一个集合中(..)判断存在.
方案一:构造动态查询
declare @sql varchar(512) set @sql = 'select code,name from B where code in ('+(select LONGNAME from A where ID = 2)+')'
execute(@sql)
方案二:调整逻辑表达
select code,name from B where charindex(','+code+',' , ','+(select LONGNAME from A where ID = 2)+',')>0
select code,name from B,(select LONGNAME from A where ID = 2) c
where charindex(code,c.LONGNAME)>0
不好意思。忘记code是int类型的了,
改下:select code,name from B,(select LONGNAME from A where ID = 2) c
where charindex(cast(code as varchar),c.LONGNAME)>0
where code in (select char(39) + LONGNAME + char(39) from A where ID = 2) 如上 就好了