如题:SQL> with aa as(
2 select 3 months, '张三' person, 2000 income from dual
3 union all
4 select 4 months, '张三' person, 3000 income from dual
5 union all
6 select 5 months, '张三' person, 4000 income from dual
7 union all
8 select 3 months, '李四' person, 3000 income from dual
9 union all
10 select 5 months, '王五' person, 4000 income from dual
11 union all
12 select 6 months, '王五' person, 3000 income from dual
13 )
14 select * from (select person,
15 decode(months,substr(to_char(sysdate,'yyyy-mm-dd'),7,1),income) "当月收入",
16 decode(months,substr(to_char(sysdate,'yyyy-mm-dd'),7,1)-1,income) "上月收入",
17 decode(months,substr(to_char(sysdate,'yyyy-mm-dd'),7,1)+1,income) "下月收入"
18 from aa
19 );
PERSON 当月收入 上月收入 下月收入
------ ---------- ---------- ----------
张三 2000
张三 3000
张三 4000
李四 3000
王五 4000
王五
6 rows selected需要实现效果如下:
PERSON 当月收入 上月收入 下月收入
------ ---------- ---------- ----------
张三 3000 2000 4000
李四 3000
王五 4000
2 select 3 months, '张三' person, 2000 income from dual
3 union all
4 select 4 months, '张三' person, 3000 income from dual
5 union all
6 select 5 months, '张三' person, 4000 income from dual
7 union all
8 select 3 months, '李四' person, 3000 income from dual
9 union all
10 select 5 months, '王五' person, 4000 income from dual
11 union all
12 select 6 months, '王五' person, 3000 income from dual
13 )
14 select * from (select person,
15 decode(months,substr(to_char(sysdate,'yyyy-mm-dd'),7,1),income) "当月收入",
16 decode(months,substr(to_char(sysdate,'yyyy-mm-dd'),7,1)-1,income) "上月收入",
17 decode(months,substr(to_char(sysdate,'yyyy-mm-dd'),7,1)+1,income) "下月收入"
18 from aa
19 );
PERSON 当月收入 上月收入 下月收入
------ ---------- ---------- ----------
张三 2000
张三 3000
张三 4000
李四 3000
王五 4000
王五
6 rows selected需要实现效果如下:
PERSON 当月收入 上月收入 下月收入
------ ---------- ---------- ----------
张三 3000 2000 4000
李四 3000
王五 4000
15 max(decode(months,substr(to_char(sysdate,'yyyy-mm-dd'),7,1),income)) "当月收入",
16 max(decode(months,substr(to_char(sysdate,'yyyy-mm-dd'),7,1)-1,income)) "上月收入",
17 max(decode(months,substr(to_char(sysdate,'yyyy-mm-dd'),7,1)+1,income)) "下月收入"
18 from aa
group by person
select 3 months, '张三' person, 2000 income from dual
union all
select 4 months, '张三' person, 3000 income from dual
union all
select 5 months, '张三' person, 4000 income from dual
union all
select 3 months, '李四' person, 3000 income from dual
union all
select 5 months, '王五' person, 4000 income from dual
union all
select 6 months, '王五' person, 3000 income from dual
)
SELECT person,
MAX(decode(months, substr(to_char(SYSDATE, 'yyyy-mm-dd'), 7, 1), income)) "当月收入",
MAX(decode(months, substr(to_char(SYSDATE, 'yyyy-mm-dd'), 7, 1) - 1, income)) "上月收入",
MAX(decode(months, substr(to_char(SYSDATE, 'yyyy-mm-dd'), 7, 1) + 1, income)) "下月收入"
FROM aa
GROUP BY person
ORDER BY person
with aa as(
select 3 months, '张三' person, 2000 income from dual
union all
select 4 months, '张三' person, 3000 income from dual
union all
select 5 months, '张三' person, 4000 income from dual
union all
select 3 months, '李四' person, 3000 income from dual
union all
select 5 months, '王五' person, 4000 income from dual
union all
select 6 months, '王五' person, 3000 income from dual
)
select person,
GREATEST("上月收入","下月收入") "当月收入",
decode("当月收入", 0 , Null,"上月收入") "上月收入",
decode("当月收入", 0 , Null,"下月收入") "下月收入"
from (select person,
max(decode(months,substr(to_char(sysdate,'yyyy-mm-dd'),7,1),income,0)) "当月收入",
max(decode(months,substr(to_char(sysdate,'yyyy-mm-dd'),7,1)-1,income,0)) "上月收入",
max(decode(months,substr(to_char(sysdate,'yyyy-mm-dd'),7,1)+1,income,0)) "下月收入"
from aa
Group By person
);
with aa as(
select 3 months, '张三' person, 2000 income from dual
union all
select 4 months, '张三' person, 3000 income from dual
union all
select 5 months, '张三' person, 4000 income from dual
union all
select 3 months, '李四' person, 3000 income from dual
union all
select 5 months, '王五' person, 4000 income from dual
union all
select 6 months, '王五' person, 3000 income from dual
)
select person,
decode("当月收入", 0 ,GREATEST("上月收入","下月收入"),"当月收入") "当月收入",
decode("当月收入", 0 , Null,"上月收入") "上月收入",
decode("当月收入", 0 , Null,"下月收入") "下月收入"
from (select person,
max(decode(months,substr(to_char(sysdate,'yyyy-mm-dd'),7,1),income,0)) "当月收入",
max(decode(months,substr(to_char(sysdate,'yyyy-mm-dd'),7,1)-1,income,0)) "上月收入",
max(decode(months,substr(to_char(sysdate,'yyyy-mm-dd'),7,1)+1,income,0)) "下月收入"
from aa
Group By person
);上面那个错了,这个试试!