select * from ItemTable where item_id in (1,2,3,4,5,6,7,8,9)
上面是需求
select * from itemtable where item_id in
(
select pid from roletable where rid in
(
select rid from usertable where uid=1
)
)我的数据表里的一列是 1,2,3,4,5,6,7,8,9 varchar类型的。
select pid from roletable where rid in
(
select rid from usertable where uid=1
) 查出来的数据是 1,2,3,4,5,6,7,8,9 varchar类型的。select * from itemtable where item_id in
()item_id 是number类型的 求解:怎么把1,2,3,4,5,6,7,8,9 varchar类型的 变成 最上面那种id列表。
上面是需求
select * from itemtable where item_id in
(
select pid from roletable where rid in
(
select rid from usertable where uid=1
)
)我的数据表里的一列是 1,2,3,4,5,6,7,8,9 varchar类型的。
select pid from roletable where rid in
(
select rid from usertable where uid=1
) 查出来的数据是 1,2,3,4,5,6,7,8,9 varchar类型的。select * from itemtable where item_id in
()item_id 是number类型的 求解:怎么把1,2,3,4,5,6,7,8,9 varchar类型的 变成 最上面那种id列表。
如果是的话
select * from itemtable where to_char(item_id) in
()item_id 是number类型的
如果每个字段存一个,如:1 或者2 或者 3
如下
select * from itemtable where item_id in
(
select to_number(rid) from usertable where uid=1
)
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
(
select to_number(pid) from roletable where rid in
(
select rid from usertable where uid=1
)
)
with t1 as
(
select 1 c1,'abc,def,mns,opq' c2 from dual
union all
select 2 c1,'a,b' c2 from dual
)select distinct c1,replace(regexp_substr(c2,'[^,]+',1,level),',',' ') c2
from t1
connect by level<=length(c2)-length(replace(c2,',',''))+1
order by c1
c1 c2
-------------------------
1 1 abc
2 1 def
3 1 mns
4 1 opq
5 2 a
6 2 b
然后把select pid from roletable where rid in
(
select rid from usertable where uid=1
) 查出来的数据前后也加上逗号结果为B,
然后在B中查找是否存在子串A,使用instr函数,如果没有找到返回0否则返回>0
例如item_id是2, A=',2,'
B=',1,2,3,4,5,6,7,8,9,',
返回大于0;例如item_id是2, A=',2,'
B=',1,3,4,5,6,7,8,9,20,',
返回0。前后加上逗号就是为了防止这里返回>0。scott@ORA11GR2> select instr(',1,2,3,4,5,6,7,8,9,', ',2,') from dual;INSTR(',1,2,3,4,5,6,7,8,9,',',2,')
----------------------------------
3scott@ORA11GR2> select instr(',1,3,4,5,6,7,8,9,20,', ',2,') from dual;INSTR(',1,3,4,5,6,7,8,9,20,',',2,')
-----------------------------------
0所以你的查询可以改成
select * from itemtable where instr(
','||(
select pid from roletable where rid in
(
select rid from usertable where uid=1
))||',', ','||item_id||',') > 0;