表a 字段1 ID
字段2 起始号码
字段3 结束号码表b 字段1 ID
字段2 号码根据a表的起止号码,查询出在b表中不存在的号码!
如 a 字段1 字段2 字段3
1 1 10 b 字段1 字段2
1 2
1 8查出来的结果应该是1,3,4,5,6,7,9,10
字段2 起始号码
字段3 结束号码表b 字段1 ID
字段2 号码根据a表的起止号码,查询出在b表中不存在的号码!
如 a 字段1 字段2 字段3
1 1 10 b 字段1 字段2
1 2
1 8查出来的结果应该是1,3,4,5,6,7,9,10
select b.no from a t, b u
where t.id = u.id and b.no < t.start_no and u.no > t.end_no;
不知道好不好使 ?
(
select 2 a from dual
union all
select 8 from dual
)
select rn from
(select a from a),
(
select rn from
(
select rownum rn from dual connect by rownum<=10
)
where rn>=1
) b
where b.rn=a(+) and a is null
--result:1
3
4
5
6
7
9
10
with a as
(select 1 id, 2 s_n, 10 e_n from dual union
select 2 id, 23, 40 from dual),
b as
(select 1 id, 5 n from dual union
select 1, 7 from dual union
select 1, 10 from dual union
select 2, 31 from dual union
select 2, 37 from dual )select *
from (
select id,l.lv
from a,(select level lv from dual connect by level <= 100 ) l where l.lv >= s_n and l.lv <= e_n
) tmp
where (tmp.id,tmp.lv) not in ( select id,n from b );
with a as
(
select 1 as col1, 1 as col2, 10 as col3 from dual
)
,
b as
(
select 1 as col1, 2 as col2 from dual
union all
select 1 as col1, 8 as col2 from dual
)
select
wm_concat(t.result_num)
from (
select (first + rownum - 1) result_num
from
(select
a.col2 first,
a.col3 last
from a, b
where a.col1 = b.col1
) connect by rownum <= last - first
) t
where t.result_num not in
(
select b.col2
from a,b
where a.col1 = b.col1
)