Select numbers not present in multiple number series in Oracle
In Oracle 10g, how do I select values which do not fit between multiple ranges. For example:
Given the numbers:
5, 10, 20, 26, 40, 50, 55, 60, 100, 200, 203, 211, 230
And the ranges:
10 - 25
50 - 55
200 - 210
The following numbers do not fit in the ranges:
5 26 40 60 100 211 230
Test data:
CREATE TABLE myseries (start_sequence NUMBER(6), end_sequence NUMBER(6));
CREATE TABLE myvalues (sequence_number NUMBER(6));
INSERT INTO myseries VALUES(10,25);
INSERT INTO myseries VALUES(50,55);
INSERT INTO myseries VALUES(200,210);
-- Insert test data
INSERT INTO myvalues VALUES(5);
INSERT INTO myvalues VALUES(10);
INSERT INTO myvalues VALUES(20);
INSERT INTO myvalues VALUES(26);
INSERT INTO myvalues VALUES(40);
INSERT INTO myvalues VALUES(50开发者_如何学C);
INSERT INTO myvalues VALUES(55);
INSERT INTO myvalues VALUES(60);
INSERT INTO myvalues VALUES(100);
INSERT INTO myvalues VALUES(200);
INSERT INTO myvalues VALUES(203);
INSERT INTO myvalues VALUES(211);
INSERT INTO myvalues VALUES(230);
select sequence_number from myvalues
where sequence_number not in (??)
select sequence_number from myvalues m
where not exists
( select 1 from myseries s where m.sequence_number between s.start_sequence
and s.end_sequence )
精彩评论