SQL查询，如果值为null，则返回1(SQL query, if value is null th

我有一个返回在我们的系统中设置的汇率值的查询。 并不是每一个订单都会有汇率（currate.currentrate），所以它返回空值。

我能得到它返回1，而不是空？

喜欢的东西也许if语句：

 if isnull(currate.currentrate) then 1 else currate.currentrate 

这里是我下面的查询。 我非常感谢您的帮助！

 SELECT     orderhed.ordernum, orderhed.orderdate, currrate.currencycode,  currrate.currentrate
 FROM         orderhed LEFT OUTER JOIN
                  currrate ON orderhed.company = currrate.company AND orderhed.orderdate = currrate.effectivedate

Answer 1:

您可以使用CASE语句。

SELECT 
    CASE WHEN currate.currentrate IS NULL THEN 1 ELSE currate.currentrate END
FROM ...

Answer 2:

你可以使用COALESCE ：

SELECT  orderhed.ordernum, 
    orderhed.orderdate, 
    currrate.currencycode,  
    coalesce(currrate.currentrate, 1) as currentrate
FROM orderhed 
LEFT OUTER JOIN currrate 
    ON orderhed.company = currrate.company 
    AND orderhed.orderdate = currrate.effectivedate

甚至IsNull()

SELECT  orderhed.ordernum, 
    orderhed.orderdate, 
    currrate.currencycode,  
    IsNull(currrate.currentrate, 1) as currentrate
FROM orderhed 
LEFT OUTER JOIN currrate 
    ON orderhed.company = currrate.company 
    AND orderhed.orderdate = currrate.effectivedate

这里是一个文章，以帮助之间做出选择COALESCE和IsNull ：

http://www.mssqltips.com/sqlservertip/2689/deciding-between-coalesce-and-isnull-in-sql-server/

Answer 3:

SELECT 
    ISNULL(currate.currentrate, 1)  
FROM ...

比获奖的答案不再繁琐，做同样的事情

https://msdn.microsoft.com/en-us/library/ms184325.aspx

Answer 4:

SELECT orderhed.ordernum, orderhed.orderdate, currrate.currencycode,  

case(currrate.currentrate) when null then 1 else currrate.currentrate end

FROM orderhed LEFT OUTER JOIN currrate ON orderhed.company = currrate.company AND orderhed.orderdate = currrate.effectivedate  

Answer 5:

a）如果你想0时值为null

SELECT isnull(PartNum,0) AS PartNumber, PartID
FROM Part

b）如果你想0时值为null否则1

SELECT 
  (CASE
    WHEN PartNum IS NULL THEN 0
    ELSE 1
  END) AS PartNumber,
  PartID
FROM Part

Answer 6:

尝试像下面...

CASE 
WHEN currate.currentrate is null THEN 1
ELSE currate.currentrate
END as currentrate

文章来源: SQL query, if value is null then return 1