I have a huge table filled with numbers (1001001..1009999) and there are certainly a bunch of unused numbers in that area. So, I could have all these numbers returned by doing:
SELECT MY_IDENTIFIER FROM MY_TABLE
that would return all used numbers. How could I get a list of unused numbers from that table? I work serverside (rhino/jaxer) with JavaScript and my database is Oracle 10开发者_StackOverflow中文版g.
Thanks,
The following query will give you what you need:
SELECT seq_num
FROM (SELECT (lvl + &&v_from - 1) seq_num
FROM (SELECT *
FROM ( SELECT LEVEL lvl
FROM DUAL
CONNECT BY LEVEL <= (&&v_to - &&v_from) + 1)))
WHERE seq_num NOT IN (SELECT my_identifier FROM my_table);
Replace &&v_to and &&v_from with your boundary numbers.
Reference: http://oraqa.com/2006/01/20/how-to-generate-sequence-numbers-between-two-numbers/
Javascript is not really related to that, isn't it? I did something similiar years before.
I had a table with all numbers in a range and joined these two tables and selected those numbers which could not be joined. A negative select.
Something like
select nr bulk collect into nrs1 from tbl1 where nr not in
(select nr from tbl2);
精彩评论