生成连续编号
生成0~99的数。
select d1.digit+(d2.digit *10) as seq from digits d1 cross join digits d2 order by seq;
生成1~888的数。
select d1.digit+(d2.digit *10)+(d3.digit *100) as seq
from digits d1 cross join digits d2 cross join digits d3
where d1.digit+(d2.digit*10)+(d3.digits*100) between 1 and 888
order by seq;
生成视图保存，并从视图中获取数据1~134.
create view sequence(seq) as
select d1.digit+(d2.digit*10)+ (d3.digit*100) as seq
from digits d1 cross join digits d2 cross join digits d3;
select seq from sequence where seq between 1 and 134;
寻找缺失的编号
寻找缺失的编号。
select seq as'缺失的编号'
from sequence
where seq between 1 and 12
and seq not in ( select seq from seqtb1);
代码优化版：
select seq as'缺失的编号'
from sequence
where seq between ( select min(seq) from seqtb1 )
and ( select max(seq) from seqtb1)
and seq not in(select seq from seqtb1);
三个人能坐得下嘛？
上图为火车座位预定情况的表。假设3个人一起去旅行，准备预定这趟火车的车票。寻找三个连续的空座位。
select s1.seat as start_seat,’~’,s2.seat as end_seat
from seats s1,seats s2
where s2.seat=s1.seat+2
and not exists
(select* from seats s3
where s3.seat between s1.seat and s2.seat
and s3.status<>'未预定');
考虑换排问题，查询在同一排的连续的三个空位。
select s1.seat as start_seat,'~',s2.seat as end_seat
from seats2 s1,seats2 s2
where s2.seat=s1.seat+2
and not exists
(select* from seats2 s3
where s3.seat between s1.seat and s2.seat
and s3.status<>'未预定'
or s1.row_id<>s2.row_id);
最多能坐下多少人
按照现在的座位情况，要求座位连续，最多可以坐多少人。
首先创建视图，存储了所有可能序列的视图。
create view sequences(start_seat,end_seat,seat_cnt)
as select s1.seat as start_seat,
s2.seat as end_seat,
s2.seat-s1.seat+1 as seat_cnt
from seats3 s1,seats3 s2
where s2.seat>=s1.seat
and not exists
(select* from seats3 s3
where(s3.seat between s1.seat and s2.seat
and s3.status<>'未预定')
or (s3.seat=s2.seat+1 and s3.status='未预定')
or (s3.seat=s1.seat-1 and s3.status='未预定'));
从试图表中查询最长序列：
select*
from sequences
where seat_cnt=
(select max(seat_cnt) from sequences);
单调递增和单调递减
假设上述的表反应了某公司的股价动态图。
查询股价单调递增的时间区间。
select m1.deal_date as start_date,'~',m2.deal_date as end_date
from mystock m1,mystock m2
where m2.deal_date>m1.deal_date
andnot exists
(select* from mystock m3,mystock m4
wherem3.deal_date between m1.deal_date and m2.deal_date
andm4.deal_date between m1.deal_date and m2.deal_date
andm3.deal_date
andm3.price>=m4.price);
网友评论