有表格存放每个顾客拥有的票. 现在想查看哪些顾客的票不是连号, 即有间隔. 并找出间隔的开始与结束数字.
- create table tickets (buyer_name char(5) not null,
- ticket_nbr int default 1 not null
- check (ticket_nbr>0),
- primary key (buyer_name,ticket_nbr)
- )
- insert into tickets values('one',2),('one',3),('one',4),('two',4),('three',1),('three',2),('three',3),('three',5)
- ,('four',1),('four',6),('four',7),('four',9),('five',10),('five',1)
解惑 1:
select t.buyer_name from tickets t group by t.buyer_name having not (max(t.ticket_nbr)-min(t.ticket_nbr)<=count(*))
解惑 2:
如果 ticket 中的数字跨越范围不是特别大, 则可以 使用序列表, 把 min(seq) 到 max(seq) 存到表 sequence(seq int) 表中, 找出各顾客在最大值和最小值之间的序列内存在于 sequence 表中的数字.
解惑 3:
创建视图, 给每个顾客的票从小到大给出一个序号. 对比上下两个号码的值, 如果之间的差的绝对值部位 1 说明他们之间不连续.
- create view tickets_v(buyer_name,ticket_nbr,ord) as
- (select t.buyer_name,t.ticket_nbr,rank() over ( partition by buyer_name order by ticket_nbr ) as ord from tickets t)
- select t1.buyer_name, t1.ticket_nbr+1 as start_nbr,t2.ticket_nbr-1 end_nbr from tickets_v t1,tickets_v t2 where t1.buyer_name=t2.buyer_name and t1.ord=t2.ord-1 and abs(t1.ticket_nbr-t2.ticket_nbr)<>1
来源: http://www.bubuko.com/infodetail-2569668.html