oracle中关于null排序的问题 问题描述: 在处理一般的数据记录中,对于数字类型的字段,在oracle的排序中,默认把null值做为 大于任何数字的类型,当然对于varchar2类型的字段,默认也是该处理方式,但是客户 要求排序的过程中,需要把null的字段默认排在前边(从小-->大)。一般的 order by xxxx,无法解决。 问题解决: 方案1: 可以使用复杂的使用sql: select * from (select a.*,rownum as my_sys_rownum from ( select deptid,nvl(BDZNAME,'''' ''''),nvl(VOLLEVEL,''''0''''),ZBRL,nvl(ZBTS, ''''0''''),nvl(FZR,''''0''''),nvl(DEPTIDDES,'''' ''''),nvl(TEL,'''' ''''),nvl(RUNSTATEDES,'''' ''''),nvl(ADDRESS,'''' ''''),BDZID from V_BDZ where rownum<2000 and ZBRL is null ) a union select b.*,rownum+(select count(*) from ( select deptid,nvl(BDZNAME,'''' ''''),nvl(VOLLEVEL,''''0''''),ZBRL,nvl(ZBTS, ''''0''''),nvl(FZR,''''0''''),nvl(DEPTIDDES,'''' ''''),nvl(TEL,'''' ''''),nvl(RUNSTATEDES,'''' ''''),nvl(ADDRESS,'''' ''''),BDZID from V_BDZ where rownum<2000 and ZBRL is null )) as my_sys_rownum from ( select deptid,nvl(BDZNAME,'''' ''''),nvl(VOLLEVEL,''''0''''),ZBRL,nvl(ZBTS, ''''0''''),nvl(FZR,''''0''''),nvl(DEPTIDDES,'''' ''''),nvl(TEL,'''' ''''),nvl(RUNSTATEDES,'''' ''''),nvl(ADDRESS,'''' ''''),BDZID from V_BDZ where rownum<2000 and ZBRL is not null order by ZBRL ) b ) order by my_sys_rownum desc 方案2: 可以利用oracle中可以对order by中对比较字段做设置的方式来实现: 如: ……order by nvl( aaa,''''-1'''')
[VB.NET程序]VB.NET中关于DataGrid颜色的自定义。 [Delphi程序]Delphi中关于TApplication类的详解 [VB.NET程序]0、(空字串)、Null、Empty、与 Nothing 的区别 [VB.NET程序]浅析VBS中的Null子类型 [其他]SQLStory摘录(七)————触摸NULL值 [ORACLE]Oracle9i中一个特殊等待事件--null event
|