如果查询时单一结果 like '%'||(select 语句)||'%'如果查询是多结果 就不清楚了 貌似不可能实现吧
select 字段 from 表 where 字段 like '%' || (select 字段 from 表 where 字段 = '条件') || '%'
select 字段 from 表 where 字段 = '条件' 如果这个查询 返回多个值 报错 ORA-01427 : 单行子查询返回多与一个行 考虑看看别的方法能实现不
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 行。
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.03scott@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
如果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 /
like '%'||(select 语句)||'%'如果查询是多结果 就不清楚了 貌似不可能实现吧
from 表
where 字段 like
'%' || (select 字段 from 表 where 字段 = '条件') || '%'
如果这个查询 返回多个值
报错 ORA-01427 : 单行子查询返回多与一个行 考虑看看别的方法能实现不
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 行。
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.03scott@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
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