Поиск только отсутствующего порядкового номера с идентификатором

Найдите недостающую последовательность. Таблица test_number содержит последовательность для каждого идентификатора. Таблица test_number_min_max содержит минимальное и максимальное количество для каждого идентификатора. Нам нужно найти пропущенное число между минимальным и максимальным числом для каждого идентификатора.

У меня есть две таблицы

CREATE TABLE test_number(id NUMBER,SEQ NUMBER,text VARCHAR2(5)) ;
INSERT INTO test_number VALUES(1,1,'AA');
INSERT INTO test_number VALUES(1,3,'CC');
INSERT INTO test_number VALUES(1,4,'DD');
INSERT INTO test_number VALUES(1,5,'EE');
INSERT INTO test_number VALUES(1,6,'FF');
INSERT INTO test_number VALUES(1,7,'GG');
INSERT INTO test_number VALUES(1,8,'HH');
INSERT INTO test_number VALUES(1,10,'JJ');
INSERT INTO test_number VALUES(2,1,'KK');
INSERT INTO test_number VALUES(2,2,'LL');
INSERT INTO test_number VALUES(2,3,'MM');
INSERT INTO test_number VALUES(2,4,'NN');
INSERT INTO test_number VALUES(2,6,'PP');
INSERT INTO test_number VALUES(2,7,'QQ');
INSERT INTO test_number VALUES(3,1,'TT');
INSERT INTO test_number VALUES(3,4,'ZZ');
INSERT INTO test_number VALUES(3,5,'XX');



create tabel test_number_min_max(id number,mn number,mx  number);
INSERT INTO test_number_min_max VALUES(1,1,12);
INSERT INTO test_number_min_max VALUES(2,1,9);
INSERT INTO test_number_min_max VALUES(3,1,5);

Ниже Query работает в оракуле, но мне нужен запрос, который выполняется в SQL serevr2008.

SELECT r id,rn seq FROM(SELECT ROWNUM rn FROM all_objects WHER ROWNUM <13),
(SELECT ROWNUM r FROM all_objects
WHERE ROWNUM <4),test_number_min_max m
WHERE r=id
AND rn >= mn
AND rn <= mx
AND(r,rn) NOT IN
(SELECT id,seq FROM test_number)

sql gaps-and-islands
person Sandesh    schedule 01.11.2014    source источник
comment
Можете ли вы отредактировать свой вопрос и показать, что вы хотите на выходе?   -  person Gordon Linoff    schedule 01.11.2014

Ответы (2)


arrow_upward
2
arrow_downward

Вы можете сделать это, сгенерировав все возможные числа, а затем найдя те, которые не совпадают. Следующее должно работать как в Oracle, так и в SQL Server:

with nums(id, seqnum, mx)  as (
      select t.id, t.mn as seqnum, t.mx
      from test_number_min_max t
      union all
      select t.id, t.mn + 1, t.mx
      from nums
      where nums.seqnum < t.mx
     )
select nums.id, nums.seqnum
from nums left outer join
     test_number tn
     on tn.id = nums.id and tn.seqnumber = nums.seqnumber
where tn.id is null;

Если между минимумом и максимумом более 100 значений, то вам нужно будет установить максимальную рекурсию. Если производительность является проблемой, вам может понадобиться другой способ генерации чисел. Вот одна из возможностей (которая также должна работать в обеих базах данных):

with nums as (
      select row_number() over (order by id) - 1 as n
      from test_number
     )
select tnmm.id, tnmm.mn + nums.n as seqnumber
from test_number_min_max tnmm join
     nums
     on tnmm.mn + nums.n <= tnmm.mx left join
     test_number tn
     on tn.id = tnmm.id and
        tn.seqnumber = tnmm.mn + nums.n
where tn.id is null;

Это предполагает, что в test_number достаточно строк для перечисления самого большого диапазона в test_number_min_max (разумное предположение, но оно может быть неверным).

person Gordon Linoff    schedule 01.11.2014
comment
Спасибо, Гордон Линофф :) - person Sandesh; 03.11.2014

arrow_upward
1
arrow_downward 

CREATE TABLE test_number(id INTEGER,SEQ INTEGER,text varchar) ;
INSERT INTO test_number VALUES
  (1,1,'AA') 
        , (1,3,'CC') , (1,4,'DD') , (1,5,'EE') , (1,6,'FF') , (1,7,'GG') 
        , (1,8,'HH') , (1,10,'JJ')
, (2,1,'KK') , (2,2,'LL') , (2,3,'MM') , (2,4,'NN') 
       , (2,6,'PP') , (2,7,'QQ')
, (3,1,'TT') 
        , (3,4,'ZZ') , (3,5,'XX')
        ;

SELECT t1.id AS "Id"
        , t1.seq+1 AS "Start"
        , t2.seq-1 AS "Stop"
        , t2.seq - t1.seq -1 AS "Gapsize"
FROM test_number t1
JOIN test_number t2 ON t2.id = t1.id AND t2.seq > t1.seq + 1
WHERE NOT EXISTS (
        SELECT * FROM test_number nx
        WHERE nx.id = t1.id
        AND nx.seq > t1.seq
        AND nx.seq < t2.seq
        );

Результат:

 Id | Start | Stop | Gapsize 
----+-------+------+---------
  1 |     2 |    2 |       1
  1 |     9 |    9 |       1
  2 |     5 |    5 |       1
  3 |     2 |    3 |       2
(4 rows)
person wildplasser    schedule 01.11.2014