谁能 看懂 下面这段代码 很有 挑战性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;oraclesql
调试欢乐多
楼主的这个sql,是先按照name分组,然后每组按照year排序,将下一行的year取出来。
name b1 b2
a 2013-1-31 2013-1-31
a 2013-1-31 2013-1-31
a 2013-1-31 2013-1-31
a 2013-1-31
b 2012-12-1 2012-12-2
b 2012-12-2 2012-12-3
b 2012-12-3 2012-12-4
b 2012-12-4
另外,最后where rk=1,就只返回第一条了。
name b1 b2
a 2013-1-31 2013-1-31
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
用这些分析函数(lead,over)写一个 sql 出来,这个 题很经典
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4
(select '用户1' as name ,'2011' as year from dual
union all
select '用户1' as name, '2012' as year from dual
union all
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
不过楼主这个东东有点华而不实,测试了一下,结果并不理想,这才是我想要的结果!欢迎讨论!
① 计算运行的总数
例如:逐行的显示一个部门的累计工资每行包括前面各行的工资总和。
② 查找一组内的百分数
例如:显示在某些部门中付给个人的总工资的百分数,将他们的工资与该部门的工资总数相除。
③ 前N个查询
例如:按照地区查找前N个工资最高的人或者前N份最高的销售款。
④ 计算正在流动的平均值
例如:将当前行的值与当前N行的值加在一起计算平均数。
⑤ 执行带等级的查询
例如:显示一个部门内一个人工资的相关等级。
引自:《oracle专家高级编程》,网上有电子版的可以下载,关于分析函数的那章节门槛非常低,适合学习