Oracle 排序中使用nulls first 或者nulls last 语法

-原理

Nulls first和nulls last是Oracle Order by支持的语法

如果Order by 中指定了表达式Nulls first则表示null值的记录将排在最前(不管是asc 还是 desc)

如果Order by 中指定了表达式Nulls last则表示null值的记录将排在最后 (不管是asc 还是 desc)

使用语法如下：

--将nulls始终放在最前

select * from zl_cbqc order by cb_ld nulls first

<wbr></wbr>

--将nulls始终放在最后

select * from zl_cbqc order by cb_ld desc nulls last

--实例

1.order by col<wbr> (asc/desc)<wbr> NULLS LAST 不管col如何排序 col的null（空值）总是在最后</wbr></wbr>

SQL> WITH tab AS(
<wbr> 2<wbr> SELECT 1 id,'小明' NAME,'开发中心' dept FROM dual<br><wbr> 3<wbr> UNION ALL<br><wbr> 4<wbr> SELECT 2,'小张','开发中心' FROM dual<br><wbr> 5<wbr> UNION ALL<br><wbr> 6<wbr> SELECT 3,'小王','开发中心' FROM dual<br><wbr> 7<wbr> UNION ALL<br><wbr> 8<wbr> SELECT 4,'小丽','研发中心' FROM dual<br><wbr> 9<wbr> )<br><wbr>10<wbr> SELECT *<br><wbr>11<wbr><wbr><wbr> FROM (<br><wbr>12<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> SELECT ID, NAME, DEPT FROM tab<br><wbr>13<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> UNION ALL<br><wbr>14<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> SELECT NULL, DEPT, DEPT FROM tab GROUP BY DEPT<br><wbr>15<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> )<br><wbr>16<wbr><wbr> ORDER BY DEPT, ID NULLS LAST<br><wbr>17<wbr> /</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

<wbr><wbr><wbr><wbr><wbr><wbr><wbr> ID NAME<wbr><wbr><wbr><wbr> DEPT<br> ---------- -------- --------<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 1 小明<wbr><wbr><wbr><wbr> 开发中心<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 2 小张<wbr><wbr><wbr><wbr> 开发中心<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 3 小王<wbr><wbr><wbr><wbr> 开发中心<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 开发中心 开发中心<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 4 小丽<wbr><wbr><wbr><wbr> 研发中心<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 研发中心 研发中心</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

已选择6行。

2.order by col<wbr> (asc/desc)<wbr> NULLS first 不管col如何排序 col的null（空值） 总是在最前</wbr></wbr>

SQL> edi
已写入 file afiedt.buf

<wbr> 1<wbr> WITH tab AS(<br><wbr> 2<wbr> SELECT 1 id,'小明' NAME,'开发中心' dept FROM dual<br><wbr> 3<wbr> UNION ALL<br><wbr> 4<wbr> SELECT 2,'小张','开发中心' FROM dual<br><wbr> 5<wbr> UNION ALL<br><wbr> 6<wbr> SELECT 3,'小王','开发中心' FROM dual<br><wbr> 7<wbr> UNION ALL<br><wbr> 8<wbr> SELECT 4,'小丽','研发中心' FROM dual<br><wbr> 9<wbr> )<br><wbr>10<wbr> SELECT *<br><wbr>11<wbr><wbr><wbr> FROM (<br><wbr>12<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> SELECT ID, NAME, DEPT FROM tab<br><wbr>13<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> UNION ALL<br><wbr>14<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> SELECT NULL, DEPT, DEPT FROM tab GROUP BY DEPT<br><wbr>15<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> )<br><wbr>16*<wbr> ORDER BY DEPT, ID NULLS first<br> SQL&gt; /</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

<wbr><wbr><wbr><wbr><wbr><wbr><wbr> ID NAME<wbr><wbr><wbr><wbr> DEPT<br> ---------- -------- --------<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 开发中心 开发中心<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 1 小明<wbr><wbr><wbr><wbr> 开发中心<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 2 小张<wbr><wbr><wbr><wbr> 开发中心<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 3 小王<wbr><wbr><wbr><wbr> 开发中心<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 研发中心 研发中心<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 4 小丽<wbr><wbr><wbr><wbr> 研发中心</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

已选择6行。

目的：

本来的目的是把部门相同的人员归在一起

SQL> edi
已写入 file afiedt.buf

<wbr> 1<wbr> WITH tab AS(<br><wbr> 2<wbr> SELECT 1 id,'小明' NAME,'开发中心' dept FROM dual<br><wbr> 3<wbr> UNION ALL<br><wbr> 4<wbr> SELECT 2,'小张','开发中心' FROM dual<br><wbr> 5<wbr> UNION ALL<br><wbr> 6<wbr> SELECT 3,'小王','开发中心' FROM dual<br><wbr> 7<wbr> UNION ALL<br><wbr> 8<wbr> SELECT 4,'小丽','研发中心' FROM dual<br><wbr> 9<wbr> )<br><wbr>10<wbr> SELECT id||name<br><wbr>11<wbr><wbr><wbr> FROM (<br><wbr>12<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> SELECT ID, NAME, DEPT FROM tab<br><wbr>13<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> UNION ALL<br><wbr>14<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> SELECT NULL, DEPT, DEPT FROM tab GROUP BY DEPT<br><wbr>15<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> )<br><wbr>16*<wbr> ORDER BY DEPT, ID NULLS last<br> SQL&gt; /</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

ID||NAME
------------------------------------------------
1小明
2小张
3小王
开发中心
4小丽
研发中心

已选择6行。