开发者

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 )
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜