–最大派
Oracle中排序时默认null最大;
处理方法:null first/last语法
(oracle9i版本后支持case语法)
–最小派
Sql Server中排序时默认null最小;
Mysql中排序时默认null最小;
处理方法:case语法,类似创建一无null的新列并优先进行排序
(不支持null first/last语法)
–实践
–age为年龄字段,null排在最后面(只对age排序:oracle升序,Mysql、Sql Serve降序)
–oracle专用nulls last语法
select * from table order by age nulls last;(升序)
select * from table order by age asc nulls last;(升序)
select * from table order by age desc nulls last;(降序)
–Mysql、Sql Server、oracle(oracle9i版本后支持)通用case语法
select * from table order by case when age is null then 1 else 0 end,age;(升序)
select * from table order by case when age is null then 0 else 1 end desc,age;(升序)
select * from table order by case when age is null then 1 else 0 end,age desc;(降序)
select * from table order by case when age is null then 0 else 1 end desc,age desc;(降序)
–age为年龄字段,null排在最前面(只对age排序:oracle降序,Mysql、Sql Serve升序)
–oracle专用nulls first语法
select * from table order by age nulls first;(升序)
select * from table order by age asc nulls first;(升序)
select * from table order by age desc nulls first;(降序)
–Mysql、Sql Server、oracle(oracle9i版本后支持)通用case语法
select * from table order by case when age is null then 0 else 1 end,age;(升序)
select * from table order by case when age is null then 1 else 0 end desc,age;(升序)
select * from table order by case when age is null then 0 else 1 end,age desc;(降序)
select * from table order by case when age is null then 1 else 0 end desc,age desc;(降序)
欢迎评论留言!