精品伊人久久大香线蕉,开心久久婷婷综合中文字幕,杏田冲梨,人妻无码aⅴ不卡中文字幕

一個(gè)網(wǎng)友的問題解決記錄。

我現(xiàn)在表有個(gè)字段是ids并且以@@分割，例如@@123@@234@@567@@.現(xiàn)在有一個(gè)select id from project查出來的結(jié)果集(如查出來id是123,234,555)現(xiàn)在我想用like匹配這個(gè)結(jié)果集，只要@@123@@234@@567@@.有一個(gè)id匹配出來出的結(jié)果集就OK

http://www.itpub.net/showthread.php?s=&threadid=723791

SQL> select * from tt;

ID
------------------------------------------------------------
@@aa@@bb@@cc@@
@@aaa@@bbb@@ccc@@

SQL> create or replace type t_object as object(
2 id varchar2(60),
3 sub_id varchar2(60)
4 );

Type created

SQL> create type t_ret_table is table of t_object;

Type created

SQL> create or replace function f_test(var_str in varchar2) return t_ret_table PIPELINED
2 as
3 var_tmp varchar2(60);
4 var_element varchar2(60);
5 begin
6 for i in (select rtrim(ltrim(id,'@@'),'@@') id from tt) loop
7 var_tmp := i.id;
8 while instr(var_tmp,'@@')>0 loop
9 var_element := substr(var_tmp,1,instr(i.id,'@@')-1);
10 var_tmp := substr(var_tmp,instr(i.id,'@@')+2,length(var_tmp));
11 pipe row(t_object(i.id,var_element));
12 end loop;
13 pipe row(t_object(i.id,var_tmp));
14 end loop;
15 return;
16 end f_test;
17 /

Function created

SQL> select id from table(f_test('a')) where sub_id in (select col from (select 'aa' col from dual union select 'bbb' col from dual union select 'ccc' from dual)) group by id;

ID
------------------------------------------------------------
aa@@bb@@cc
aaa@@bbb@@ccc

SQL>select * from table(f_test('a'));

ID SUB_ID
------------------------------------------------------------ ------------------------------------------------------------
aa@@bb@@cc aa
aa@@bb@@cc bb
aa@@bb@@cc cc
aaa@@bbb@@ccc aaa
aaa@@bbb@@ccc bbb
aaa@@bbb@@ccc ccc

6 rows selected.

SQL>select id from table(f_test('a')) where sub_id in (
select substr(col,instr(col,',',1,rownum)+1,instr(col,',',1,rownum+1)-instr(col,',',1,rownum)-1) col from (
select ','||'aa,aaa,bbb'||',' col from dual
) connect by rownum<length(translate(col,','||col,','))
)
group by id