谜题58_间隔之LAG

连接到: 
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

CREATE TABLE Tickets
(buyer_name CHAR(5) NOT NULL, 
 ticket_nbr INTEGER, 
 PRIMARY KEY (buyer_name, ticket_nbr));

INSERT INTO Tickets
SELECT 'a', 2 FROM dual UNION ALL
SELECT 'a', 3 FROM dual UNION ALL
SELECT 'a', 4 FROM dual UNION ALL
SELECT 'b', 4 FROM dual UNION ALL
SELECT 'c', 1 FROM dual UNION ALL
SELECT 'c', 2 FROM dual UNION ALL
SELECT 'c', 3 FROM dual UNION ALL
SELECT 'c', 4 FROM dual UNION ALL
SELECT 'c', 5 FROM dual UNION ALL
SELECT 'd', 1 FROM dual UNION ALL
SELECT 'd', 6 FROM dual UNION ALL
SELECT 'd', 7 FROM dual UNION ALL
SELECT 'd', 9 FROM dual UNION ALL
SELECT 'e', 10  FROM dual;

SELECT buyer_name, l_seq + 1, ticket_nbr - 1
  FROM (SELECT buyer_name,
               nvl(lag(ticket_nbr)
                   over(PARTITION BY buyer_name ORDER BY ticket_nbr),
                   0) l_seq,
               ticket_nbr
          FROM tickets n1)
 WHERE nvl(l_seq, 0) <> ticket_nbr - 1
 ORDER BY 1, 2;

BUYER_NAME	   L_SEQ+1 TICKET_NBR-1
--------------- ---------- ------------
a			 1	      1
b			 1	      3
d			 2	      5
d			 8	      8
e			 1	      9

已用时间:  00: 00: 00.00
SQL>