1.随机返回N条记录
select * from (select ename,job from emp order by dbms_random.value()) where rownum <= N;
2.处理空值排序
Nulls first和nulls last是Oracle Order by支持的语法
如果Order by 中指定了表达式Nulls first则表示null值的记录将排在最前(不管是asc 还是 desc)
如果Order by 中指定了表达式Nulls last则表示null值的记录将排在最后 (不管是asc 还是 desc)
select * from emp order by common nulls last;--null值靠后
select * from emp order by common nulls first;--null值靠前
3.查询跳过表中的偶数行
rank() over(partition by col1 order by col2), dense_rank() over(同前), row_number() over(同前) 的区别
相同点:可以在各个分组内从1开始排序
rank over ()可以实现对表排名,特点是order by后的列相同的值是并列。如两名学生成绩相同排名:1 2 2 4 5
dense_rank()和rank over()很像,但学生成绩并列后并不会空出并列所占的名次。如:1 2 2 3 4
row_number这个函数不需要考虑是否并列,哪怕根据条件查询出来的数值相同也会进行连续排名。如:1 2 3 4 5
select ename from (select row_number() over (order by ename) rn,ename from emp) x
where mod(rn,2)=1;--查询奇数行
注:partition by和group by有何区别?
partition by用于给结果集进行分区。
partition by只是将原始数据进行名次排列(记录数不变)
group by是对原始数据进行聚合统计(记录数可能变少, 每组返回一条)
4.连续求和
over(条件)
select ename,sal,sum(sal) over(),sum(sal) over(order by ename) from emp;
sum(sal) over(order by ename)指的是连续求和,是以ename来排序的。若有两个这样的窗口函数,
以后面的排序为主。
5.分部门连续求和
select deptno,sal,sum(sal) over (partition by deptno order by ename) as s from emp;--分部门连续求和
sum(sal) over (partition by deptno order by ename)--分部门求和
6.得到当前行上一行或者下一行的数据
lead(field,n) over()主要作用是从某一个集合的当前cursor位置算起,按照某种顺序将第下第n个位置的某一个字段值取出来,默认是往下1个位置。
lag() over()就是取当前顺序的上一行记录。
select ename,sal,lead(sal) over(order by sal) aa,lag(sal) over(order by sal) bb from emp;
7.根据子串分组
select to_char(hiredate,'yyyy'),avg(sal) from emp group by to_char(hiredate,'yyyy');
8.确定一年内的天数
select add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y') from dual;