sql查询进阶

准备数据 

insert into t_income values('201701',1,1000);
insert into t_income values('201701',2,2000);
insert into t_income values('201701',3,3000);
insert into t_income values('201701',4,4000);
insert into t_income values('201702',1,3000);
insert into t_income values('201702',2,1000);
insert into t_income values('201702',3,5000);
insert into t_income values('201702',4,4000);
insert into t_income values('201703',1,3000);
insert into t_income values('201703',2,3000);
insert into t_income values('201703',3,5000);
insert into t_income values('201703',4,4000);
insert into t_income values('201704',1,2000);
insert into t_income values('201704',2,3000);
insert into t_income values('201704',3,5000);
insert into t_income values('201704',4,4000);
commit;

1、取上一条数据和下一条数据
在一行中显示当月收入、上月收入、下月收入

select m,s
,lag(s,1,null) over(order by m) last_m
,lead(s,1,null) over(order by m) next_m
from (select m,sum(income) as s from t_income group by m)t

2、查询明细及合计

select case gf when 0  then m  else   '合计' end  as m
,s from (
select m, sum(income) s,grouping(m) gf from t_income group by rollup(m)
) t

3、生成联系序号
select level from dual connect by level<=10;