SELECT SPARE_NAME,MADE_BY,MODEL,SPARE_TYPE,
(SELECT SUM(TOTAL_COUNT) AS TOTAL_COUNT FROM OM_SPARE ,OM_SPARE_REPLACEMENT where OM_SPARE.ID=OM_SPARE_REPLACEMENT.SPARE_ID) AS TOTAL_COUNTFROM OM_SPARE我想要的结果是:如果SUM(TOTAL_COUNT)为空的话那么我要用0来填充。请教各为高手怎么写!我记得在SQL里面可以这样写 isnull(SUM(TOTAL_COUNT),0)在oracle不会写请大哥帮帮忙
(SELECT SUM(TOTAL_COUNT) AS TOTAL_COUNT FROM OM_SPARE ,OM_SPARE_REPLACEMENT where OM_SPARE.ID=OM_SPARE_REPLACEMENT.SPARE_ID) AS TOTAL_COUNTFROM OM_SPARE我想要的结果是:如果SUM(TOTAL_COUNT)为空的话那么我要用0来填充。请教各为高手怎么写!我记得在SQL里面可以这样写 isnull(SUM(TOTAL_COUNT),0)在oracle不会写请大哥帮帮忙
(SELECT DECODE(SUM(TOTAL_COUNT),NULL,0) AS TOTAL_COUNT FROM OM_SPARE ,OM_SPARE_REPLACEMENT where OM_SPARE.ID=OM_SPARE_REPLACEMENT.SPARE_ID) AS TOTAL_COUNTFROM OM_SPARE用DECODE
SELECT SPARE_NAME,MADE_BY,MODEL,SPARE_TYPE,
(SELECT DECODE(SUM(TOTAL_COUNT),NULL,0,SUM(TOTAL_COUNT)) AS TOTAL_COUNT FROM OM_SPARE ,OM_SPARE_REPLACEMENT where OM_SPARE.ID=OM_SPARE_REPLACEMENT.SPARE_ID) AS TOTAL_COUNTFROM OM_SPARE
用法:
select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值
sign()函数根据某个值是0、正数还是负数,分别返回0、1、-1
(SELECT nvl(SUM(TOTAL_COUNT),0) AS TOTAL_COUNT FROM OM_SPARE ,OM_SPARE_REPLACEMENT where OM_SPARE.ID=OM_SPARE_REPLACEMENT.SPARE_ID) AS TOTAL_COUNT
FROM OM_SPARE用nvl(a,b),a为空,就是b的值