Oracle数据库中对null值的排序及mull与空字符串的区别

数据库  /  管理员 发布于 6年前   199

order by排序之null值处理方法
在对业务数据排序时候，发现有些字段的记录是null值，这时排序便出现了有违我们使用习惯的数据大小顺序问题。在Oracle中规定，在Order by排序时缺省认为null是最大值，所以如果是ASC升序则被排在最后，而DESC降序则排在最前。所以，为何分析数据的直观性方便性，我们需要对null的记录值进行相应处理。
这是四种oracle排序中NULL值处理的方法：
1、使用nvl函数
语法：Nvl(expr1, expr2)
    若EXPR1是NULL,t返回EXPR2,否t返回EXPR1.

  SELECT NAME,NVL(TO_CHAR(COMM),'NOT APPLICATION') FROM TABLE1;

nvl函数可以在输入参数为空时转换为一特定值，如
nvl(person_name,“未知”)表示若person_name字段值为空时返回“未知”，如不为空则返回person_name的字段值。
通过这个函数可以定制null的排序位置。
2、使用decode函数
 
decode函数比nvl函数更强大，同样它也可以将输入参数为空时转换为一特定值，如
decode(person_name,null,“未知”, person_name)表示当person_name为空时返回“未知”，如不为空则返回person_name的字段值。
通过此函数也可以定制null的排序位置。

3、使用nulls first 或者nulls last 语法，最简单常用的方法。

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

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

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

使用方法举例如下：
将nulls始终放在最前：

select * from tbl order by field nulls first

将nulls始终放在最后：

select * from tbl order by field desc nulls last

4、使用case 语法
 
Case语法是Oracle 9i后开始支持的，是一个比较灵活的语法，同样在排序中也可以应用
如：

select * from students order by (case person_name      when null then       '未知'      else       person_name     end)

表示在person_name字段值为空时返回'未知'，如果不为空则返回person_name
通过case语法同样可以定制null的排序位置。
 
项目实例：

  !defined('PATH_ADMIN') && exit('Forbidden'); class mod_gcdownload {   public static function get_gcdownload_datalist($start = 0,$rowsperpage = PAGE_ROWS, $datestart = '',$dateend = '',$ver = '',$coopid = '',$subcoopid = '',$sortfield = '', $sorttype = '', $pid = 123456789, $plat = 'abcdefg'){     $sql = '';     $condition = empty($datestart) ? " WHERE 1=1 " : " WHERE t.statistics_date >= '$datestart' AND t.statistics_date <= '$dateend'";     if($ver)     {       $condition .= " AND t.edition='$ver'";     }     if($coopid)     {       $condition .= " AND t.suco_coopid=$coopid";     }     if($subcoopid)     {       $condition .= " AND t.suco_subcoopid=$subcoopid";     }     if($sortfield && $sorttype){       $condition .= " ORDER BY t.{$sortfield} {$sorttype} NULLS LAST";     }elseif($sortfield){       $condition .= " ORDER BY t.{$sortfield} desc NULLS LAST";     }else{       $condition .= " ORDER BY t.statistics_date desc NULLS LAST";     }     $finish = $start + $rowsperpage;     $joinsqlcollection = "(SELECT tc.coop_name, tsc.suco_name, tsc.suco_coopid,tsc.suco_subcoopid, s.edition, s.new_user, d.one_user, d.three_user, d.seven_user, s.statistics_date FROM (((pdt_stat_newuser_{$pid}_{$plat} s LEFT JOIN pdt_days_dl_remain_{$pid}_{$plat} d ON s.statistics_date=d.new_date AND s.subcoopid=d.subcoopid AND s.edition=d.edition )LEFT JOIN [email protected]@JTINFO tsc ON s.subcoopid=tsc.suco_subcoopid) LEFT JOIN [email protected]@JTINFO tc ON tsc.suco_coopid=tc.coop_id))";     $sql = "SELECT * FROM (SELECT tb_A.*, ROWNUM AS rn FROM (SELECT t.* FROM $joinsqlcollection t {$condition} ) tb_A WHERE ROWNUM <= {$finish} ) tb_B WHERE tb_B.rn>{$start} ";     $countsql = "SELECT COUNT(*) AS totalrows, SUM(t.new_user) AS totalnewusr,SUM(t.one_user) AS totaloneusr,SUM(t.three_user) AS totalthreeusr,SUM(t.seven_user) AS totalsevenusr FROM $joinsqlcollection t {$condition} ";     $db = oralceinit(1);     $stidquery = $db->query($sql,false);     $output = array();     while($row = $db->FetchArray($stidquery, $skip = 0, $maxrows = -1))     {         $output['data'][] = array_change_key_case($row,CASE_LOWER);     }     $count_stidquery = $db->query($countsql,false);     $row = $db->FetchArray($count_stidquery, $skip = 0, $maxrows = -1);     $output['total']= array_change_key_case($row,CASE_LOWER);     //echo "<br />".($sql)."<br />";     return $output;       }  } 

Null与空字符串' '的区别
含义解释：
问：什么是NULL？
答：在我们不知道具体有什么数据的时候，也即未知，可以用NULL，我们称它为空，ORACLE中，含有空值的表列长度为零。
ORACLE允许任何一种数据类型的字段为空，除了以下两种情况：
1、主键字段（primary key），
2、定义时已经加了NOT NULL限制条件的字段
说明：
1、等价于没有任何值、是未知数。
2、NULL与0、空字符串、空格都不同。
3、对空值做加、减、乘、除等运算操作，结果仍为空。
4、NULL的处理使用NVL函数。
5、比较时使用关键字用“is null”和“is not null”。
6、空值不能被索引，所以查询时有些符合条件的数据可能查不出来，count(*)中，用nvl(列名,0)处理后再查。
7、排序时比其他数据都大（索引默认是降序排列，小→大），所以NULL值总是排在最后。
使用方法：

SQL> select 1 from dual where null=null; 

没有查到记录

SQL> select 1 from dual where null=''; 

没有查到记录

SQL> select 1 from dual where ''=''; 

没有查到记录

SQL> select 1 from dual where null is null; 1 --------- 1 SQL> select 1 from dual where nvl(null,0)=nvl(null,0); 1 --------- 1 

对空值做加、减、乘、除等运算操作，结果仍为空。

SQL> select 1+null from dual; SQL> select 1-null from dual; SQL> select 1*null from dual; SQL> select 1/null from dual; 

查询到一个记录.
注：这个记录就是SQL语句中的那个null
设置某些列为空值

update table1 set 列1=NULL where 列1 is not null; 

现有一个商品销售表sale，表结构为：

month　　　 char(6)　　　　　 --月份 sell　　　　number(10,2)　　　--月销售金额 create table sale (month char(6),sell number); insert into sale values('200001',1000); insert into sale values('200002',1100); insert into sale values('200003',1200); insert into sale values('200004',1300); insert into sale values('200005',1400); insert into sale values('200006',1500); insert into sale values('200007',1600); insert into sale values('200101',1100); insert into sale values('200202',1200); insert into sale values('200301',1300); insert into sale values('200008',1000); insert into sale(month) values('200009');（注意：这条记录的sell值为空） commit; 

共输入12条记录

SQL> select * from sale where sell like '%'; MONTH SELL ------ --------- 200001 1000 200002 1100 200003 1200 200004 1300 200005 1400 200006 1500 200007 1600 200101 1100 200202 1200 200301 1300 200008 1000 

查询到11记录.
结果说明：
查询结果说明此SQL语句查询不出列值为NULL的字段
此时需对字段为NULL的情况另外处理。

SQL> select * from sale where sell like '%' or sell is null; SQL> select * from sale where nvl(sell,0) like '%'; MONTH SELL ------ --------- 200001 1000 200002 1100 200003 1200 200004 1300 200005 1400 200006 1500 200007 1600 200101 1100 200202 1200 200301 1300 200008 1000 200009 

查询到12记录.
Oracle的空值就是这么的用法，我们最好熟悉它的约定，以防查出的结果不正确。

但对于char 和varchar2类型的数据库字段中的null和空字符串是否有区别呢？
作一个测试：

create table test (a char(5),b char(5)); SQL> insert into test(a,b) values('1','1'); SQL> insert into test(a,b) values('2','2'); SQL> insert into test(a,b) values('3','');--按照上面的解释，b字段有值的 SQL> insert into test(a) values('4'); SQL> select * from test; A B ---------- ---------- 1 1 2 2 3 4 

SQL> select * from test where b='';

 ----按照上面的解释，应该有一条记录，但实际上没有记录
未选定行

SQL> select * from test where b is null; 

----按照上面的解释，应该有一跳记录，但实际上有两条记录。

A B ---------- ---------- 3 4 SQL>update table test set b='' where a='2'; SQL> select * from test where b=''; 

未选定行

SQL> select * from test where b is null; A B ---------- ---------- 2 3 4 

测试结果说明，对char和varchar2字段来说，''就是null；但对于where 条件后的'' 不是null。
对于缺省值，也是一样的！