SELECT '不存在' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM T_USER WHERE REGEXP_INSTR('A、B',REAL_NAME) > 0
WITH t_user AS ( SELECT '1' id, 'A' real_name FROM dual ) SELECT 'A' FROM t_user t1 WHERE 'A' NOT IN ( SELECT t2.real_name FROM t_user t2 WHERE REGEXP_INSTR('A、B',real_name) > 0) ;
REGEXP_INSTR 这是啥标识符? 结果是显示名字,不是显示“不存在”
with a as (select 'A' name from dual union all select 'B' name from dual ), b as (select a.name,b.real_name from a,t_user b where a.name=b.real_name(+)) select a.name from b where b.real_name is null;
with a as (select 'A' name from dual union all select 'B' name from dual ) select a.name from a where a.name not in (select real_name from t_user);
select decode((select count(*) from t_user where real_name=&name),0,'不存在',null) from dualSQL> select * from num_tb where id=20101001 2 / ID ---------- 20101001 20101001 20101001SQL> select decode((select count(*) from num_tb where id=&id),0,'不存在',null) from dual 2 / 输入 id 的值: 2 原值 1: select decode((select count(*) from num_tb where id=&id),0,'不存在',null) from dual 新值 1: select decode((select count(*) from num_tb where id=2),0,'不存在',null) from dualDECODE ------ 不存在SQL>
with t_user as ( -- 用户表 select 1 id, 'a' realname from dual ), t2 as ( -- 拆分输入的名字 select 'a,b' inputname, rownum rn from dual connect by rownum<=length('a,b') ), t3 as ( select substr(inputname, rn, instr(inputname||',',',',rn+1)-1) inputname from t2 where substr(','||inputname,rn,1)=',' ) select inputname noname from t3 where inputname not in (select realname from t_user) /NONAME ------------ b
SELECT '1' id, 'A' real_name FROM dual
)
SELECT 'A'
FROM t_user t1
WHERE 'A' NOT IN (
SELECT t2.real_name
FROM t_user t2
WHERE REGEXP_INSTR('A、B',real_name) > 0) ;
select 'B' name from dual ),
b as (select a.name,b.real_name from a,t_user b where a.name=b.real_name(+))
select a.name from b where b.real_name is null;
select 'B' name from dual )
select a.name from a where a.name not in (select real_name from t_user);
结果显示名字,是显示B?这是个行转列的问题,偶没有简单的办法,除非自定义函数。
2 / ID
----------
20101001
20101001
20101001SQL> select decode((select count(*) from num_tb where id=&id),0,'不存在',null) from dual
2 /
输入 id 的值: 2
原值 1: select decode((select count(*) from num_tb where id=&id),0,'不存在',null) from dual
新值 1: select decode((select count(*) from num_tb where id=2),0,'不存在',null) from dualDECODE
------
不存在SQL>
with t_user as ( -- 用户表
select 1 id, 'a' realname from dual
),
t2 as ( -- 拆分输入的名字
select 'a,b' inputname, rownum rn from dual connect by rownum<=length('a,b')
),
t3 as (
select substr(inputname, rn, instr(inputname||',',',',rn+1)-1) inputname
from t2 where substr(','||inputname,rn,1)=','
)
select inputname noname from t3 where inputname not in (select realname from t_user)
/NONAME
------------
b