Oracle中高级查询

素颜马尾好姑娘i 2022-07-14 06:23 56阅读 0赞

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;