17,140
社区成员




谁能 看懂 下面这段代码 很有 挑战性
select * from (select name, year b1, lead(year) over(partition by name order by year) b2, lead(m,2) over(partition by name order by year) b3,rank()over(partition by name order by year) rk from t) where rk=1;
select '用户1' as name, '2013' as year from dual
union all
select '用户2' as name,'2011' as year from dual
union all
select '用户2' as name, '2012' as year from dual
union all
select '用户2' as name, '2013' as year from dual
union all
select '用户3' as name ,'2012' as year from dual
union all
select '用户3' as name, '2013' as year from dual
)
select name,
sum(case when year=to_char(add_months(sysdate,-24),'yyyy') then year else null end) b1,
sum(case when year=to_char(add_months(sysdate,-12),'yyyy') then year else null end) b2,
sum(case when year=to_char(sysdate,'yyyy') then year else null end) b3
from test
group by name
order by name
不过楼主这个东东有点华而不实,测试了一下,结果并不理想,这才是我想要的结果!欢迎讨论!