现有表1:
ID(number) PRODUCTS(VARCHAR2(1024 CHAR))
297812 ,284433,284434,
297816 ,284433,284434,
301716 ,261330,
297940 ,284433,表2:
PARENTID(number) PRODUCTID(number)
111 284433
111 284434
222 261330即表1中的products包含一个或者多个表2中的productid值,且用"," 分隔, 现在想得到表1中products 包含表2中parentid=111的productid的行,即想得到如下结果:
ID PRODUCTS
297812 ,284433,284434,
297816 ,284433,284434,
297940 ,284433,请高手指点!在线等! 谢谢!
ID(number) PRODUCTS(VARCHAR2(1024 CHAR))
297812 ,284433,284434,
297816 ,284433,284434,
301716 ,261330,
297940 ,284433,表2:
PARENTID(number) PRODUCTID(number)
111 284433
111 284434
222 261330即表1中的products包含一个或者多个表2中的productid值,且用"," 分隔, 现在想得到表1中products 包含表2中parentid=111的productid的行,即想得到如下结果:
ID PRODUCTS
297812 ,284433,284434,
297816 ,284433,284434,
297940 ,284433,请高手指点!在线等! 谢谢!
with tmp1 as
(
select '297812 ,284433,284434,' products from dual union all
select '297816 ,284433,284434,' products from dual union all
select '301716 ,261330,' products from dual union all
select '297940 ,284433,' products from dual
),
tmp2 as
(
select 111 pid, 284433 prodid from dual union all
select 111 pid, 284434 prodid from dual union all
select 222 pid, 261330 prodid from dual
)
select distinct tmp1.products
from tmp1, tmp2
where instr(tmp1.products, tmp2.prodid) <> 0
and tmp2.pid = 111;PRODUCTS
---------------------------------
297940 ,284433,
297812 ,284433,284434,
297816 ,284433,284434,
重新把表的各字段用"|"分隔如下:
表1:
ID(number)| PRODUCTS(VARCHAR2(1024 CHAR))
297812 |,284433,284434,
297816 |,284433,284434,
301716 |,261330,
297940 |,284433,表2:
PARENTID(number)| PRODUCTID(number)
111 | 284433
111 | 284434
222 | 261330不知道能不能用循环语句来实现?请指点!
(
select '297812 ,284433,284434,' products from dual union all
select '297816 ,284433,284434,' products from dual union all
select '301716 ,261330,' products from dual union all
select '297940 ,284433,' products from dual
),
tmp2 as
(
select 111 pid, 284433 prodid from dual union all
select 111 pid, 284434 prodid from dual union all
select 222 pid, 261330 prodid from dual
)
select distinct a.* from tmp1 a ,tmp2 b
where instr(','||a.products||',',','||b.prodid||',')>0
and b.pid=111
with tmp1 as
(
select '297812' id, ',284433,284434,' products from dual union all
select '297816' id, ',284433,284434,' products from dual union all
select '301716' id, ',261330,' products from dual union all
select '297940' id, ',284433,' products from dual
),
tmp2 as
(
select 111 pid, 284433 prodid from dual union all
select 111 pid, 284434 prodid from dual union all
select 222 pid, 261330 prodid from dual
)
select distinct tmp1.id, tmp1.products
from tmp1, tmp2
where instr(tmp1.products, tmp2.prodid) <> 0
and tmp2.pid = 111;ID PRODUCTS
--------- ----------------------
297812 ,284433,284434,
297816 ,284433,284434,
297940 ,284433, 数据多的话,用到了distinct,性能可能就慢点了
select distinct 表1.id, 表1.products
from 表1, 表2
where instr(表1.products, 表2.productid) <> 0
and 表2.PARENTID = 111;另:如果我要把表1中所有的284433都更新为表2中parentid=222的261330,sql该怎么写呢?要求不能直接用261330,必须用parentid=222.
declare
cursor csr is select id,products from 表1;
tmpval varchar2(1000);
begin
for cur in csr loop
tmpid:=cur.id;
val:=cur.products;
select to_char(PRODUCTID) into tmpval from 表2 where parentid=222;
update 表1 set products=replace(products,'284433',tmpval) where products like '%284433%'; end loop;
commit;
end;
/ 这样可以把表1中的284433都替换成表2中的parentid=222的productid,我的2个表中数据都比较多,只需要在cursor csr is select id,products from 表1; 和 select to_char(PRODUCTID) into tmpval from 表2 where parentid=222; 语句中加入相应的条件即可. 个人觉得用cursor来循环解决问题比较好.另外楼上的语句: select * from 表1,表2 where 表1.PRODUCTS like '%'||表2.PRODUCTID ||'%' and 表2.PARENTID='111' 会出现很多重复的记录.
declare
cursor csr is select id,products from 表1;
tmpval varchar2(1000);
begin
for cur in csr loop
select to_char(PRODUCTID) into tmpval from 表2 where parentid=222;
update 表1 set products=replace(products,'284433',tmpval) where products like '%284433%'; end loop;
commit;
end;
/