tb_filts
a b c
I 5 10
E 6 8
 


select * from tb_data
 where code in(select code from tb_data where code>=min(b) and code<=max(b) )
and code not in(select code from tb_data where code>=min(c) and code<=max(c) )

------解决方案--------------------
建立一个游标变量用于读取tb_filts表中的数据;
打开游标后, 然后循环得到字段code_low,code_high的值 .根据每条数据的值编写符合这些信息的sql,返回code值.
然后将sql拼接在一起.


------解决方案--------------------
SQL code

SELECT m.*
  FROM tb_data m,
       (SELECT MIN(DECODE(t.include_exclude_indicator, 'I', t.code_low)) i_code_low,
               MAX(DECODE(t.include_exclude_indicator, 'I', t.code_high)) i_code_high,
               MIN(DECODE(t.include_exclude_indicator, 'E', t.code_low)) e_code_low,
               MAX(DECODE(t.include_exclude_indicator, 'E', t.code_high)) e_code_high
          FROM tb_filts t) n
 WHERE m.code >= n.i_code_low
   AND m.code <= n.i_code_high
   AND (m.code < n.e_code_low OR m.code > e_code_high)

------解决方案--------------------
in的条件写反了，改下
SQL code

SELECT m.*
  FROM tb_data m,
       (SELECT MAX(DECODE(t.include_exclude_indicator, 'I', t.code_low)) i_code_low,
               MIN(DECODE(t.include_exclude_indicator, 'I', t.code_high)) i_code_high,
               MIN(DECODE(t.include_exclude_indicator, 'E', t.code_low)) e_code_low,
               MAX(DECODE(t.include_exclude_indicator, 'E', t.code_high)) e_code_high
          FROM tb_filts t) n
 WHERE m.code >= n.i_code_low
   AND m.code <= n.i_code_high
   AND (m.code < n.e_code_low OR m.code > e_code_high)

------解决方案--------------------

SQL code

with tb_data as(
select 1 code,'a' data1,'a' data2 from dual
union all
select 2,'b','b' from dual
union all
select 3,'c','c' from dual
union all
select 4,'d','d' from dual
union all
select 5,'e','e' from dual
union all
select 6,'f','f' from dual
union all
select 7,'g','g' from dual
union all
select 8,'h','h' from dual
union all
select 9,'i','i' from dual
union all
select 10,'j','j' from dual
union all
select 11,'k','k' from dual
),tb_filts as(
select 'I' include_exclude_indicator,5 code_low,10 code_high from dual
union all
select 'E',6,8 from dual
)
select * from tb_data t1 where 
exists (select 1 from tb_filts t2 
         where t2.include_exclude_indicator='I' 
              and t1.code>=t2.code_low and t1.code<=t2.code_high)
and not exists (select 1 from tb_filts t2 
         where t2.include_exclude_indicator='E' 
              and t1.code>=t2.code_low and t1.code<=t2.code_high)


      CODE DATA1 DATA2
---------- ----- -----
        10 j     j
         9 i     i
         5 e     e

------解决方案--------------------
SQL code

with tb_data as(
select 1 code,'a' data1,'a' data2 from dual
union all
select 2,'b','b' from dual
union all
select 3,'c','c' from dual
union all
select 4,'d','d' from dual
union all
select 5,'e','e' from dual
union all
select 6,'f','f' from dual
union all
select 7,'g','g' from dual
union all
select 8,'h','h'