关于NVL函数

Question

我在NVL中使用了一个简单的SQL，但是查询没有正确地显示结果。我的要求是，如果Shipment_refnum_qual_gid为'ULA/SAO.UL_ONU‘，则取此refnum的值，否则在结果中显示NULL。

示例查询如下所示。

SELECT NVL (SRF.SHIPMENT_REFNUM_VALUE, 'NULL') 
FROM SHIPMENT_REFNUM SRF, SHIPMENT SH 
WHERE SRF.SHIPMENT_GID= SH.SHIPMENT_GID AND SH.SHIPMENT_GID = 'ULA/SAO.5000057288' 
AND SRF.SHIPMENT_REFNUM_QUAL_GID = 'ULA/SAO.UL_ONU

Answer 1

MySQL没有NVL函数。相反，请使用IFNULL

select ifnull(SRF.SHIPMENT_REFNUM_VALUE, 'NULL')
  from SHIPMENT_REFNUM SRF, SHIPMENT SH
  where SRF.SHIPMENT_GID= SH.SHIPMENT_GID
    and SH.SHIPMENT_GID = 'ULA/SAO.5000057288'
    and SRF.SHIPMENT_REFNUM_QUAL_GID = 'ULA/SAO.UL_ONU'

编辑

正如您在注释中所说的，对于SHIPMENT中的一个或多个值，在SHIPMENT_REFNUM中没有匹配的条目，但您希望包括这些条目，则需要一个外部联接：

select ifnull(SRF.SHIPMENT_REFNUM_VALUE, 'NULL')
  from SHIPMENT_REFNUM SRF
  right outer join SHIPMENT SH on SRF.SHIPMENT_GID= SH.SHIPMENT_GID
  where SH.SHIPMENT_GID = 'ULA/SAO.5000057288'
    and SRF.SHIPMENT_REFNUM_QUAL_GID = 'ULA/SAO.UL_ONU'