有两个表:表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中行数不确定。
字段: 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中行数不确定。
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) )
打开游标后, 然后循环得到字段code_low,code_high的值 .根据每条数据的值编写符合这些信息的sql,返回code值.
然后将sql拼接在一起.
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)
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)
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
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
是'I'的时候,取范围小的
比如:
include_exclude_indicator code_low code_high
I 5 20
I 7 16
那么I的条件就是7-16
是'E'的时候和'I'相反.
你说哪里不行?
include_exclude_indicator code_low code_high
I 5 20
I 22 26
就会把21也查出来的
SQL> WITH tb_data AS(
2 SELECT 1 CODE,'A' DATA1,'A' DATA2 FROM DUAL UNION ALL
3 SELECT 21,'K','K' FROM DUAL
4 ),tb_filts as(
5 SELECT 'I' include_exclude_indicator,5 code_low,20 code_high FROM DUAL UNION ALL
6 SELECT 'I' include_exclude_indicator,22 code_low,26 code_high FROM DUAL
7 )
8 SELECT m.*
9 FROM tb_data m,
10 (SELECT MAX(DECODE(t.include_exclude_indicator, 'I', t.code_low)) i_code_low,
11 MIN(DECODE(t.include_exclude_indicator, 'I', t.code_high)) i_code_high,
12 MIN(DECODE(t.include_exclude_indicator, 'E', t.code_low)) e_code_low,
13 MAX(DECODE(t.include_exclude_indicator, 'E', t.code_high)) e_code_high
14 FROM tb_filts t) n
15 WHERE m.code >= n.i_code_low
16 AND m.code <= n.i_code_high
17 AND (m.code < n.e_code_low OR m.code > e_code_high)
18 ; CODE DATA1 DATA2
---------- ----- -----
你看检索出21了吗
对楼主的问题有个疑问,如果tb_filts 中有多行记录中都有个I,那么 是取交集还是并集?比如有两行为
I 4 10
I 3 11
那么最后整合后是满足4——10 还是3——11,纠结。
SELECT 'I' include_exclude_indicator,5 code_low,20 code_high FROM DUAL UNION ALL
6 SELECT 'I' include_exclude_indicator,22 code_low,26 code_high FROM DUAL
因为I的条件中不包括21
2 SELECT 1 CODE,'A' DATA1,'A' DATA2 FROM DUAL UNION ALL
3 SELECT 21 CODE,'B' DATA1,'B' DATA2 FROM DUAL UNION ALL
4 SELECT 35 CODE,'C' DATA1,'C' DATA2 FROM DUAL UNION ALL
5 SELECT 41 CODE,'D' DATA1,'D' DATA2 FROM DUAL UNION ALL
6 SELECT 46 CODE,'E' DATA1,'E' DATA2 FROM DUAL
7 ),tb_filts as(
8 SELECT 'E' include_exclude_indicator,30 code_low,40 code_high FROM DUAL
9 UNION ALL
10 SELECT 'E' include_exclude_indicator,42 code_low,45 code_high FROM DUAL
11 UNION ALL
12 SELECT 'I' include_exclude_indicator,1 code_low,41 code_high FROM DUAL
13 )
14 SELECT *
15 FROM tb_data t1
16 WHERE NOT EXISTS
17 (SELECT 1 FROM tb_filts t2
18 WHERE (t2.include_exclude_indicator = 'I' AND
19 (t1.code < t2.code_low OR t1.code > t2.code_high))
20 OR (t2.include_exclude_indicator = 'E' AND
21 t1.code >= t2.code_low AND t1.code <= t2.code_high))
22 ; CODE DATA1 DATA2
---------- ----- -----
1 A A
21 B B
41 D D
begin
if (select count(*) from tb_filts where include_exclude_indicator = 'I') = 0 then
select * from tb_data where code between (select min(code_low) from tb_filts) and (select max(code_max) from tb_filts);
elsif (select count(*) from tb_filts where include_exclude_indicator = 'e') = 0) then
select * from tb_date where code < (select min(code_low) from tb_filts)
or code > (select max(code_low) from tb_filts);
else
select * from tb_date where code between(select min(code_low) from tb_filts where include_exclude_indicator = 'I')
and(select max(code_low) from tb_filts where include_exclude_indicator = 'I');
and (code < (select min(code_low) from tb_filts where include_exclude_indicator = 'e')
or(select max(code_max) from tb_filts where include_exclude_indicator = 'e'));
end if ;
end;