>oracle中关于null排序的问题</P>>问题描述：<BR>在处理一般的数据记录中，对于数字类型的字段，在oracle的排序中，默认把null值做为<BR>大于任何数字的类型，当然对于varchar2类型的字段，默认也是该处理方式，但是客户<BR>要求排序的过程中，需要把null的字段默认排在前边（从小－－>大）。一般的<BR>order by xxxx，无法解决。</P>- {. B& w+ `# h* ^( Y9 ?>问题解决：<BR>方案1：<BR>可以使用复杂的使用sql：</P>% Z+ ?' P4 C0 V. Z>select * from <BR>(select a.*,rownum as my_sys_rownum from (<BR>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 </P>>and ZBRL is null</P>( e; E5 W5 s# k+ u2 K>) a<BR>union<BR>select b.*,rownum+(select count(*) from (<BR>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 </P>, z5 W, y/ H1 n& ], z>and ZBRL is null</P>6 V1 E# V3 b1 m+ y& f/ |>)) as my_sys_rownum from (<BR>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 </P>7 Y1 ^% m f- F+ _- w& b8 ? E>and ZBRL is not null order by ZBRL <BR>) b<BR>)<BR>order by my_sys_rownum desc</P>( F4 N$ y+ Z: A" O5 m5 n>方案2：<BR>可以利用oracle中可以对order by中对比较字段做设置的方式来实现：<BR> 如： ……order by nvl( aaa,'-1')</P><BR>| 欢迎光临 数学建模社区-数学中国 (http://www.madio.net/) | Powered by Discuz! X2.5 |