表A字段如下
month person income
月份 人员 收入要求用一个SQL语句(注意是一个)的处所有人(不区分人员)每个月及上月和下月的总收入。要求列表输出为
月份 当月收入 上月收入 下月收入
month person income
月份 人员 收入要求用一个SQL语句(注意是一个)的处所有人(不区分人员)每个月及上月和下月的总收入。要求列表输出为
月份 当月收入 上月收入 下月收入
(select income from A as b where a.month=b.month) as current_month_sum,
(select income from A as b where b.month=a.month-1) as prior_month_sum,
(select income from A as b where b.month=a.month+1) as next_month_sum
from A as a
from T T1,
(select salary, (month + 1) month from T T2) T22,
(select salary, (month + 1) month from T T3) T33
where T1.month = (+)T22.month
and T1.month = (+)T33.month
select person,
max(decode(month,&输入年月,total_income,0)) as 当月收入,
max(decode(month,&输入年月-1,total_income,0)) as 上月收入,
max(decode(month,&输入年月+1,total_income,0)) as 下月收入
from(
select to_char(month,'yyyymm') as month,person,sum(income) as total_income
from A
where to_char(month,'yyyymm')=&输入年月
group by to_char(month,'yyyymm'),person
)
group by person