如下SQL语句,我想取前5条最大的数据:
select year,unitname,substr(comname,0,2) as sheng,bzze as baoe, premium as baofei,
yjpk as peikuan from project_caseinfo where rownum <= 5 and riskcode = '2509'
order by baoe desc
使用上面的SQL,查询出来的数据排序错误,需要将SQL改成先排序后 取前5条数据。如下:
select m.year,m.unitname,m.sheng,m.baoe,m.baofei,m.peikuan
from (select year,unitname,substr(comname,0,2) as sheng,bzze as baoe, premium as baofei,
yjpk as peikuan from project_caseinfo where riskcode = '2509'
order by baoe desc nulls last) m where rownum <= 5
这样查询出来的结果即为正确的。
当使用order by 排序时,
racle默认为 null 最大。
升序排列,默认情况下,null值排后面。
降序排序,默认情况下,null值排前面。
在Oracle中使用order by进行查询排序时,如果排序字段中有空值(null),排序结果可能会达不到我们想要的结果。如:
select * from test order by age desc;
按照age字段降序排序,结果如下:
将sql语句改为
select * from test order by age desc nulls last;
即可将null值排到最后。”nulls last”将空值放在后面,相反的”nulls first”将空值记录放在前面