有两个表：
表1为数据表：tb_data,
字段： code,
data1,
data2
表2为条件表: tb_filts,
字段：include_exclude_indicator,(值为I或E：值为I时，表示条件包含；值为E时，表示排除)
code_low,
code_high
假设表tb_filts有2条记录：
I 5 10
E 6 8
那么就希望查询tb_data中的code大于等于5而且小于等于10的数据，但不包括code大于等于6小于等于8的数据：
类似如下：
select * from tb_data
where code in(select code from tb_data where code>=5 and code<=10 )
and code not in(select code from tb_data where code>=6 and code<=8 )
tb_filts中行数不确定。
------解决方案--------------------
- SQL code
tb_filtsa b cI 5 10E 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 dualunion allselect 2,'b','b' from dualunion allselect 3,'c','c' from dualunion allselect 4,'d','d' from dualunion allselect 5,'e','e' from dualunion allselect 6,'f','f' from dualunion allselect 7,'g','g' from dualunion allselect 8,'h','h' from dualunion allselect 9,'i','i' from dualunion allselect 10,'j','j' from dualunion allselect 11,'k','k' from dual),tb_filts as(select 'I' include_exclude_indicator,5 code_low,10 code_high from dualunion allselect '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 dualunion allselect 2,'b','b' from dualunion allselect 3,'c','c' from dualunion allselect 4,'d','d' from dualunion allselect 5,'e','e' from dualunion allselect 6,'f','f' from dualunion allselect 7,'g','g' from dualunion allselect 8,'h','h' from dualunion allselect 9,'i','i' from dualunion allselect 10,'j','j' from dualunion allselect 11,'k','k' from dual),tb_filts as(select 'I' include_exclude_indicator,5 code_low,10 code_high from dualunion allselect '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