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

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

select * from tbl order by field nulls first

select * from tbl order by field desc nulls last

select *

  from students

  order by ( case person_name

       when null then

        '未知'

       else

        person_name

      end )

   !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 ; 

   }

}

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;

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

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;

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

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

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