指定的字符串数组是: aaa bbb ccc ddd eee--先把字符串数组导入到一个表里面,建设表名是T,字段是f1,然后执行如下查询: select f1 from t where f1 not in (select 字段1 from 表1)
通过minus比较 with t1 as ( select 'aaa' c1 from dual union all select 'bbb' c1 from dual union all select 'ccc' c1 from dual union all select 'ddd' c1 from dual ),t2 as ( select 'aaa' c1 from dual union all select 'ccc' c1 from dual )select c1 from t1 minus select c1 from t2 c1 ----------------------- 1 bbb 2 ddd
给定的字符串是('aaa','bbb','ccc','ddd','eee')select c1 from t1 minus select ('aaa','bbb','ccc','ddd','eee') as c1 from t2这样写报错啊
--直接写SQL是不能比较表中的数据和外部数据的,外部数据必须要有对象依托
怎么不能比较?select f1 from t where f1 not in ('aaa','bbb','ccc','ddd','eee')这样不就行吗?我现在想反过来比,因为f1里比('aaa','bbb','ccc','ddd','eee')少个'eee'。 --直接写SQL是不能比较表中的数据和外部数据的,外部数据必须要有对象依托
我的意思就是你反过来比在SQL语句里面是不能比较的,除非你能使用JAVA构造出类似2楼的临时表字符串with t1 as ( select 'aaa' c1 from dual union all select 'bbb' c1 from dual union all select 'ccc' c1 from dual union all select 'ddd' c1 from dual )--然后结合下面的SQL语句查询 select * from t1 where c1 not in (select 字段1 from 表1)
在程序里面还不简单么 直接循环判断就可以了 用不着sql
select * from 表1 where 字段 not in ('aaa','bbb','ccc','ddd','eee');
select distinct t1.pj_cd,t2.DESCRIPTION from ( select t.pj_cd as pj_cd from ( select G_PJ_CD_1 as PJ_CD from dual union select G_PJ_CD_2 as PJ_CD from dual union select G_PJ_CD_3 as PJ_CD from dual union select G_PJ_CD_4 as PJ_CD from dual union select G_PJ_CD_5 as PJ_CD from dual ) t minus (SELECT distinct t.pj_code as pj_code from MORI_ARAP_REPORT_TEMP t where t.request_id=G_REQUEST_ID) ) t1给你个参考
1、数据量少,select * from a where a.col not in ('aaa','bbb','ccc','ddd','eee'); 2、数据量多,得建临时表b,select a.*,b.col from a,b where a.col=b.col(+) and b.col is null 3、用PL/SQL中的游标,遍历实现
aaa
bbb
ccc
ddd
eee--先把字符串数组导入到一个表里面,建设表名是T,字段是f1,然后执行如下查询:
select f1 from t where f1 not in (select 字段1 from 表1)
with t1 as
(
select 'aaa' c1 from dual union all
select 'bbb' c1 from dual union all
select 'ccc' c1 from dual union all
select 'ddd' c1 from dual
),t2 as
(
select 'aaa' c1 from dual union all
select 'ccc' c1 from dual
)select c1 from t1
minus
select c1 from t2 c1
-----------------------
1 bbb
2 ddd
minus
select ('aaa','bbb','ccc','ddd','eee') as c1 from t2这样写报错啊
--直接写SQL是不能比较表中的数据和外部数据的,外部数据必须要有对象依托
--直接写SQL是不能比较表中的数据和外部数据的,外部数据必须要有对象依托
(
select 'aaa' c1 from dual union all
select 'bbb' c1 from dual union all
select 'ccc' c1 from dual union all
select 'ddd' c1 from dual
)--然后结合下面的SQL语句查询
select * from t1 where c1 not in (select 字段1 from 表1)
(
select t.pj_cd as pj_cd from
(
select G_PJ_CD_1 as PJ_CD from dual
union select G_PJ_CD_2 as PJ_CD from dual
union select G_PJ_CD_3 as PJ_CD from dual
union select G_PJ_CD_4 as PJ_CD from dual
union select G_PJ_CD_5 as PJ_CD from dual
) t
minus
(SELECT distinct t.pj_code as pj_code from MORI_ARAP_REPORT_TEMP t
where t.request_id=G_REQUEST_ID) ) t1给你个参考
2、数据量多,得建临时表b,select a.*,b.col from a,b where a.col=b.col(+) and b.col is null
3、用PL/SQL中的游标,遍历实现