NVL(expr1,expr2)
NVL
lets you replace null (returned as a 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 Database implicitly converts one to the other. If they are cannot be converted implicitly, the database returns an error. The implicit conversion is implemented as follows:
-
If
expr1
is character data, then Oracle Database convertsexpr2
to the datatype ofexpr1
before comparing them and returnsVARCHAR2
in the character set ofexpr1
. -
If
expr1
is numeric, then Oracle determines which argument has the highest numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype.
表一:
订单号 产品编号 物料编号 库存数
0001 1254 11212 0
0001 1254 11213 0
0001 1254 11257 0
表二:
订单号 产品编号 物料编号 库存数
0001 1254 11212 9
0001 1254 11213 50
我想要的结果是:
用一条查询语句得到如下结果
订单号 产品编号 物料编号 库存数
0001 1254 11212 9
0001 1254 11213 50
0001 1254 11257 0
Select a.订单号,a.产品编号,a.物料编号,
nvl(b.库存数,0) as 库存数
from 表一 a Left Join 表二 b on
a.订单号=b.订单号 and a.产品编号=b.产品编号
and a.物料编号=b.物料编号