1、ORDER BY 中關於NULL的處理
缺省處理,Oracle在Order by 時認為null是最大值,所以如果是ASC升序則排在最後,DESC降序則排在最前。
當然,你也可以使用nulls first 或者nulls last 語法來控制NULL的位置。 Nulls first和nulls last是Oracle Order by支持的語法 如果Order by 中指定了表達式Nulls first則表示null值的記錄將排在最前(不管是asc 還是 desc) 如果Order by 中指定了表達式Nulls last則表示null值的記錄將排在最後 (不管是asc 還是 desc) 使用語法如下:
--將nulls始終放在最前 select * from 表名 order by 列名 nulls first
--將nulls始終放在最後 select * from 表名 order by 列名 last
2、幾種排序的寫法 單列升序:
select<column_name> from <table_name> order by <column_name>; (默認升序,即使不寫ASC)
單列降序:select <column_name> from <table_name> order by <column_name> desc;
多列升序:select <column_one>, <column_two> from <table_name> order by <column_one>, <column_two>;
多列降序:select <column_one>, <column_two> from <table_name> order by <column_one> desc, <column_two> desc;
多列混合排序:select <column_one>, <column_two> from <table_name> order by <column_one> desc, <column_two> asc;
sql 無規律排序
select * from ( select 1 a,1 b from dual union all select 1 a,2 b from dual union all select 10 a,2 b from dual union all select 100 a,2 b from dual ) xxx order by decode(a,100,0,a)
等價如下語句
select 100 a,2 b from dual union all select * from ( select 1 a,1 b from dual union all select 1 a,2 b from dual union all select 10 a,2 b from dual ) xxx