Oracle数据库中对null值的排序及mull与空字符串的区
order by排序之null值处理方法
在对业务数据排序时候,发现有些字段的记录是null值,这时排序便出现了有违我们使用习惯的数据大小顺序问题。在Oracle中规定,在Order by排序时缺省认为null是最大值,所以如果是ASC升序则被排在,而DESC降序则排在最前。所以,为何分析数据的直观性方便性,我们需要对null的记录值进行相应处理。
这是四种oracle排序中NULL值处理的方法
1、使用nvl函数
语法Nvl(expr1, expr2)
若EXPR1是NULL,則返回EXPR2,否則返回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 tbl_subcooperator@JTUSER1.NET@JTINFO tsc ON s.subcoopid=tsc.suco_subcoopid) LEFT JOIN tbl_cooperator@JTUSER1.NET@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 1null 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值为空) mit;
共输入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。
对于缺省值,也是一样的!
编程语言
- 甘肃哪有关键词排名优化购买方式有哪些
- 甘肃SEO如何做网站优化
- 河南seo关键词优化怎么做电话营销
- 北京SEO优化如何做QQ群营销
- 来宾百度关键词排名:提升您网站曝光率的关键
- 卢龙关键词优化:提升您网站排名的策略与技巧
- 山东网站优化的注意事项有哪些
- 四川整站优化怎样提升在搜索引擎中的排名
- 疏附整站优化:提升网站性能与用户体验的全新
- 海南seo主要做什么工作售后服务要做到哪些
- 荣昌百度网站优化:提升您网站的搜索引擎排名
- 河北seo网站排名关键词优化如何做SEO
- 江西优化关键词排名推广售后保障一般有哪些
- 古浪SEO优化:提升你的网站可见性
- 西藏网站排名优化怎么把网站排名在百度首页
- 如何提升阳东百度快照排名:详尽指南