product表中有个varchar2型字段: delivery_ids,里面放置的是另一个表delivery的主键列表,数据格式如下:
,1,2,3,4,11,
现在我要根据delivery_ids来关联查询delivery的信息。
该如何写这个SQL语句?我现在这样写,但是不行:
select *
from delivery d
where to_char(d.id) in
(select REPLACE('''0 ' || t.support_dm || ' 0''', ',', ''', ''')
from product t
where t.id = 901);子查询
select REPLACE('''0 ' || t.support_dm || ' 0''', ',', ''', ''')
from product t
where t.id = 901
的结果是:'0 ', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10', ' 0'
但是,oracle把它看作一个字符串了。所以主查询是无法查询到数据的。
各位大虾给个方案,谢了!
,1,2,3,4,11,
现在我要根据delivery_ids来关联查询delivery的信息。
该如何写这个SQL语句?我现在这样写,但是不行:
select *
from delivery d
where to_char(d.id) in
(select REPLACE('''0 ' || t.support_dm || ' 0''', ',', ''', ''')
from product t
where t.id = 901);子查询
select REPLACE('''0 ' || t.support_dm || ' 0''', ',', ''', ''')
from product t
where t.id = 901
的结果是:'0 ', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10', ' 0'
但是,oracle把它看作一个字符串了。所以主查询是无法查询到数据的。
各位大虾给个方案,谢了!
where exists(select 1 from product t
where instr(delivery_ids,','||d.id||',')>0
and id=901)
where exists(select 1 from product t
where instr(delivery_ids,','''||d.id||''',')>0
and id=901)
from delivery d
where instr(select REPLACE('''0 ' || t.support_dm || ' 0''', ',', ''', ''')
from product t
where t.id = 901),','||to_char(d.id)||',',1)>0
from delivery d ,product t
where instr( t.support_dm,','||d.id||',')>0
and t.id = 901;