Oracle Order By排序的用法

Asc升序

不使用asc

with main as (select 3 as id, '张三' as name from DUAL
              union
              select 4 as id, '李四' as name from DUAL
              union
              select 2 as id, '王五' as name from DUAL
              union
              select 1 as id, '周六' as name from DUAL
              union
              select 6 as id, '田七' as name from DUAL
              union
              select 5 as id, '吴八' as name from DUAL)
select id, name from main order by id;

使用order by asc排序

with main as (select 3 as id, '张三' as name from DUAL
              union
              select 4 as id, '李四' as name from DUAL
              union
              select 2 as id, '王五' as name from DUAL
              union
              select 1 as id, '周六' as name from DUAL
              union
              select 6 as id, '田七' as name from DUAL
              union
              select 5 as id, '吴八' as name from DUAL)
select id, name from main order by id asc;

通过以上两个案列的比较，可以得知：默认情况下，无论是否指定ASC，ORDER BY子句都按升序对行进行排序

desc降序

with main as (select 3 as id, '张三' as name from DUAL
              union
              select 4 as id, '李四' as name from DUAL
              union
              select 2 as id, '王五' as name from DUAL
              union
              select 1 as id, '周六' as name from DUAL
              union
              select 6 as id, '田七' as name from DUAL
              union
              select 5 as id, '吴八' as name from DUAL)
select id, name from main order by id desc;

按照多列排序

对id进行升序，name进行降序

with main as (select 3 as id, '张三' as name from DUAL
              union
              select 2 as id, '李四' as name from DUAL
              union
              select 1 as id, '王五' as name from DUAL
              union
              select 1 as id, '周六' as name from DUAL
              union
              select 6 as id, '田七' as name from DUAL
              union
              select 5 as id, '吴八' as name from DUAL)
select id, name from main order by id,name desc;

按照列位置排序

with main as (select 3 as id, '张三' as name from DUAL
              union
              select 2 as id, '李四' as name from DUAL
              union
              select 1 as id, '王五' as name from DUAL
              union
              select 1 as id, '周六' as name from DUAL
              union
              select 6 as id, '田七' as name from DUAL
              union
              select 5 as id, '吴八' as name from DUAL)
select id, name from main order by 1,2 desc;

null值排序

null排序，可以使用NULLS FIRST/LAST指定null在前还是在后

不使用NULLS FIRST/LAST

with main as (select 3 as id, '张三' as name,'' as address from DUAL
              union
              select 2 as id, '李四' as name,'流沙河' as address from DUAL
              union
              select 1 as id, '王五' as name,'' as address from DUAL
              union
              select 1 as id, '周六' as name,'高老庄' as address from DUAL
              union
              select 6 as id, '田七' as name,'' as address from DUAL
              union
              select 5 as id, '吴八' as name,'花果山' as address from DUAL)
select id, name,address from main order by address desc;

使用nulls fisrt，将null值放置在前

with main as (select 3 as id, '张三' as name,'' as address from DUAL
              union
              select 2 as id, '李四' as name,'流沙河' as address from DUAL
              union
              select 1 as id, '王五' as name,'' as address from DUAL
              union
              select 1 as id, '周六' as name,'高老庄' as address from DUAL
              union
              select 6 as id, '田七' as name,'' as address from DUAL
              union
              select 5 as id, '吴八' as name,'花果山' as address from DUAL)
select id, name,address from main order by address desc nulls first ;

使用nulls last,将null值放置在后

with main as (select 3 as id, '张三' as name,'' as address from DUAL
              union
              select 2 as id, '李四' as name,'流沙河' as address from DUAL
              union
              select 1 as id, '王五' as name,'' as address from DUAL
              union
              select 1 as id, '周六' as name,'高老庄' as address from DUAL
              union
              select 6 as id, '田七' as name,'' as address from DUAL
              union
              select 5 as id, '吴八' as name,'花果山' as address from DUAL)
select id, name,address from main order by address desc nulls last ;

根据函数或者表达式排序

这里简单使用upper()做个演示

with main as (select 3 as id, 'zhangsan' as name,'' as address from DUAL
              union
              select 2 as id, 'ZHOUWU' as name,'流沙河' as address from DUAL
              union
              select 1 as id, 'WANGERXIAO' as name,'' as address from DUAL
              union
              select 1 as id, 'lisi' as name,'高老庄' as address from DUAL
              union
              select 6 as id, 'wangmazi' as name,'' as address from DUAL
              union
              select 5 as id, 'LISI' as name,'花果山' as address from DUAL)
select id, name,address from main order by name desc ;

使用upper(name),将name转为大写，在进行排序

with main as (select 3 as id, 'zhangsan' as name,'' as address from DUAL
              union
              select 2 as id, 'ZHOUWU' as name,'流沙河' as address from DUAL
              union
              select 1 as id, 'WANGERXIAO' as name,'' as address from DUAL
              union
              select 1 as id, 'lisi' as name,'高老庄' as address from DUAL
              union
              select 6 as id, 'wangmazi' as name,'' as address from DUAL
              union
              select 5 as id, 'LISI' as name,'花果山' as address from DUAL)
select id, name,address from main order by upper(name) desc ;

order by dbms_random.value()随机排序

从信息中每次都随机取两行数据，就可以使用dbms_random.value() 对数据进行随机排序，再使用rownum取排序后的数据。

with main as (select 3 as id, 'zhangsan' as name,'' as address from DUAL
              union
              select 2 as id, 'ZHOUWU' as name,'流沙河' as address from DUAL
              union
              select 1 as id, 'WANGERXIAO' as name,'' as address from DUAL
              union
              select 1 as id, 'lisi' as name,'高老庄' as address from DUAL
              union
              select 6 as id, 'wangmazi' as name,'' as address from DUAL
              union
              select 5 as id, 'LISI' as name,'花果山' as address from DUAL)
select id, name, address
from (select id, name, address from main order by DBMS_RANDOM.VALUE())
where ROWNUM <= 2;