null字的是字段中的值, 如你的a表里有个字段叫ab,且ab的值是null则你可以用 select nvl(a.ab,'0') from a ; 输出:当a表中的ab字段为null时,则输出0,否则输出ab字段的非空值。
select nvl(字段,0) from 表名
NVL Syntax nvl::= Purpose NVL lets you replace a null (blank) with a string in the results of a query. If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1. The arguments expr1 and expr2 can have any datatype. If their datatypes are different, then Oracle converts expr2 to the datatype of expr1 before comparing them. The datatype of the return value is always the same as the datatype of expr1, unless expr1 is character data, in which case the return value’s datatype is VARCHAR2 and is in the character set of expr1.Examples The following example returns a list of employee names and commissions, substituting "Not Applicable" if the employee receives no commission: SELECT last_name, NVL(TO_CHAR(commission_pct), ’Not Applicable’) "COMMISSION" FROM employees WHERE last_name LIKE ’B%’ ORDER BY last_name; LAST_NAME COMMISSION ------------------------- ---------------------------------------- Baer Not Applicable Baida Not Applicable Banda .1 Bates .15 Bell Not Applicable Bernstein .25 Bissot Not Applicable Bloom .2 Bull Not Applicable
可以用下面的方法 select decode(company_id, null, 0, company_id) from user; select nvl(company_id,0) from user;
刚开始学习Oracle 请大家学习。
select nvl(字段A,0) from 表名; 意思是当字段A为null的时候,显示为0,否则显示为字段Aselect nvl(字段A,0) from 表名; == select decode(字段A,null,0,字段A) from 表名; == select case when 字段A is null then 0 else 字段A end from 表名;
null字的是字段中的值, 如你的a表里有个字段叫ab,且ab的值是null 则你可以用 select nvl(a.ab,'0') from a ; 输出:当a表中的ab字段为null时,则输出0,否则输出ab字段的非空值。
--字段的值如果是null则输出0
还是有点不大明白
比方有A表,怎么把A表中的NULL值用0输出?
输出:当a表中的ab字段为null时,则输出0,否则输出ab字段的非空值。
Syntax
nvl::=
Purpose
NVL lets you replace a null (blank) with a string in the results of a query. If expr1 is
null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1. The
arguments expr1 and expr2 can have any datatype. If their datatypes are
different, then Oracle converts expr2 to the datatype of expr1 before comparing
them.
The datatype of the return value is always the same as the datatype of expr1,
unless expr1 is character data, in which case the return value’s datatype is
VARCHAR2 and is in the character set of expr1.Examples
The following example returns a list of employee names and commissions,
substituting "Not Applicable" if the employee receives no commission:
SELECT last_name, NVL(TO_CHAR(commission_pct), ’Not Applicable’)
"COMMISSION" FROM employees
WHERE last_name LIKE ’B%’
ORDER BY last_name;
LAST_NAME COMMISSION
------------------------- ----------------------------------------
Baer Not Applicable
Baida Not Applicable
Banda .1
Bates .15
Bell Not Applicable
Bernstein .25
Bissot Not Applicable
Bloom .2
Bull Not Applicable
select decode(company_id, null, 0, company_id) from user;
select nvl(company_id,0) from user;
意思是当字段A为null的时候,显示为0,否则显示为字段Aselect nvl(字段A,0) from 表名;
==
select decode(字段A,null,0,字段A) from 表名;
==
select case when 字段A is null then 0 else 字段A end from 表名;
输出:当a表中的ab字段为null时,则输出0,否则输出ab字段的非空值。