(2/14) oracle查询优化的改写笔记第一章给查询结果排序

超、凢脫俗 2023-02-18 11:53 47阅读 0赞

-- 以指定的次序返回查询结果
    select empno, ename ,hiredate from emp where deptno=10 order by hiredate asc;
    
    -- 数据只能替换order by后面的字段
    select empno, ename ,hiredate from emp where deptno=10 order by 3 asc;
    
    -- 按子串排序 substr 从后面截取 前五行数据按照第二列sal排序
    select ename as 名称,  sal as 工资 , substr(job, -2) as 尾号
    from emp 
    where rownum<=5 
    order by 2;
    
    -- 从表中随机返回n条记录 正确写法 先随机排序，再取出数据
    select empno , ename 
    from (select empno , ename from emp order by dbms_random.value())
    where rownum<=3;
    
    select *
    from (select empno,ename from emp where rownum<=3 order by dbms_random.value) order by 1;
    
    select empno, ename from (
    	select empno,ename from emp order by dbms_random.value()
    	)
    	where rownum<=3;
    	
    -- translate转换字符
    select translate('ab你好bcadefg','abcdefg','1234567') as new_str from dual;
    select translate('ab你好bcadefg','labcdefg','l') as new_str from dual;
    
    -- 按数字和字母混合字符串中的字母排序
    create or replace view v2_6 
    as 
    select empno || ' ' || ename as data from emp;
    select * from v2_6;
    
    select data, translate(data,'- 0123456789','-') as ename from v2_6 order by 2;
    
    -- 处理排序空值
    select ename, mgr from emp where deptno=10 order by mgr asc;
    select ename, mgr from emp where deptno=10 order by mgr asc nulls first;
    select ename, mgr from emp where deptno=10 order by mgr desc ;
    select ename, mgr from emp where deptno=10 order by mgr desc nulls last;
    
    -- 根据条件取不同列中的值来排序
    select empno, ename ,
    	case when sal>=1000 and sal<2000 then 1 else 2 end as 级别,
    	sal as 工资
    from emp
    where deptno=30
    order by 3, 4;