谜题58_间隔之LAG

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsCREATE TABLE Tickets(buyer_name CHAR(5) NOT NULL,  ticket_nbr INTEGER,  PRIMARY KEY (buyer_name, ticket_nbr));INSERT INTO TicketsSELECT 'a', 2 FROM dual UNION ALLSELECT 'a', 3 FROM dual UNION ALLSELECT 'a', 4 FROM dual UNION ALLSELECT 'b', 4 FROM dual UNION ALLSELECT 'c', 1 FROM dual UNION ALLSELECT 'c', 2 FROM dual UNION ALLSELECT 'c', 3 FROM dual UNION ALLSELECT 'c', 4 FROM dual UNION ALLSELECT 'c', 5 FROM dual UNION ALLSELECT 'd', 1 FROM dual UNION ALLSELECT 'd', 6 FROM dual UNION ALLSELECT 'd', 7 FROM dual UNION ALLSELECT 'd', 9 FROM dual UNION ALLSELECT '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      1b 1      3d 2      5d 8      8e 1      9已用时间:  00: 00: 00.00SQL>