Намиране само на липсващ пореден номер с id

Намерете липсващата последователност. Таблица 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 работи в oracle, но искам заявка, която се изпълнява в 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