SQL中 WHERE 后 LIKE %（select语句）%

HAOXIN19871207 2010-05-26 05:07:34

SQL中 WHERE 后 LIKE %（select语句）% 如何实现

...全文

301 15 打赏收藏转发到动态举报

写回复

用AI写文章

15 条回复

切换为时间正序

请发表友善的回复…

发表回复

HAOXIN19871207 2010-05-28

打赏
举报

xiexie

wh62592855 2010-05-27

打赏
举报

如果where中的子查询返回多行的话可是尝试使用下面的方法

SQL> select * from t2;

 

        ID NAME

---------- --------------------

         1 a

         2 b

         3 c

         1 d

SQL> create or replace function trans(text1 in number)

  2  return varchar2

  3  is

  4  text2 varchar2(100) := '';

  5  cursor c_result is select name from t2 where id = text1;

  6  begin

  7  for i in c_result loop

  8  text2 := text2||i.name||',';

  9  end loop;

 10  text2 := rtrim(text2,',');

 11  return text2;

 12  end;

 13  /

 

Function created

 

SQL> 

SQL> select trans(1) from dual;

 

TRANS(1)

--------------------------------------------------------------------------------

a,d

 

SQL> select * from t2 where instr((select trans(1) from dual),name) > 0;

 

        ID NAME

---------- --------------------

         1 a

         1 d

luoyoumou 2010-05-27

打赏
举报

scott@SZTYORA> SELECT '%'||substr(t2.ename,1,2)||'%' sub_ename

  2                FROM emp t2

  3                WHERE rownum = 1;



SUB_ENAME

--------------------

%AD%



已选择 1 行。



scott@SZTYORA> SELECT *

  2  FROM emp t1

  3  WHERE ename like  (SELECT '%'||substr(t2.ename,1,2)||'%' sub_ename

  4                FROM emp t2

  5                WHERE rownum = 1 );



     EMPNO ENAME                JOB                       MGR HIREDATE            SAL       COMM     DEPTNO

---------- -------------------- ------------------ ---------- ------------ ---------- ---------- ----------

      7876 ADAMS                CLERK                    7788 23-MAY-87          1100                 30



已选择 1 行。



已用时间:  00: 00: 00.03



scott@SZTYORA> SELECT *

  2  FROM emp t1

  3  WHERE ename like  '%AD%';



     EMPNO ENAME                JOB                       MGR HIREDATE            SAL       COMM     DEPTNO

---------- -------------------- ------------------ ---------- ------------ ---------- ---------- ----------

      7876 ADAMS                CLERK                    7788 23-MAY-87          1100                 30



已选择 1 行。



已用时间:  00: 00: 00.01

luoyoumou 2010-05-27

打赏
举报

scott@SZTYORA> SELECT *

  2  FROM emp t1

  3  WHERE ename like  (SELECT '%'||substr(t2.ename,1,2)||'%' sub_ename

  4                FROM emp t2

  5                WHERE rownum = 1 );



     EMPNO ENAME                JOB                       MGR HIREDATE            SAL       COMM     DEPTNO

---------- -------------------- ------------------ ---------- ------------ ---------- ---------- ----------

      7876 ADAMS                CLERK                    7788 23-MAY-87          1100                 30



已选择 1 行。