SQL 查询中的 sql-server、IF 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1783806/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sql-server, IF statement within SQL Query
提问by Alex
I'm having trouble with using an IFstatement within this sql-server query.
我IF在此 sql-server 查询中使用语句时遇到问题。
What I want:
我想要的是：
If salesHeader.[Ship-to Name]isn't null, I need to return that AS DeliveryName, if it is null, return something else.
如果salesHeader.[Ship-to Name]不为空，我需要返回那个 AS DeliveryName，如果它为空，返回别的东西。
Is there a way to do this with an ifstatement in a query?
有没有办法用if查询中的语句来做到这一点？
SELECT
poHeader.No_ AS PONumber,
poHeader.[Buy-from Vendor No_] AS VendorNumber,
poHeader.[Document Date] AS DocDate,
salesHeader.GiftMessage,
salesHeader.[Delivery Comment] AS DeliveryComment,
salesHeader.[Shipment Date] AS DeliveryDate,
IF salesHeader.[Ship-to Name] IS NOT NULL
salesHeader.[Ship-to Name] AS DeliveryName
ELSE
poHeader.[Ship-to Name] AS DeliveryName
END
FROM
dbo.[Crocus Live$Purch_ orders for e-mailing] AS poForEmailing
LEFT OUTER JOIN
dbo.[Crocus Live$Purchase Header] AS poHeader ON poForEmailing.No_ = poHeader.No_
INNER JOIN
dbo.[Crocus Live$Vendor] AS vendor ON poHeader.[Buy-from Vendor No_] = vendor.No_
LEFT OUTER JOIN
dbo.[Crocus Live$Sales Header] AS salesHeader ON salesHeader.No_ = dbo.fnGetSalesOrderNumber(poHeader.No_)
回答by gbn
Use CASE, not IF
使用 CASE，而不是 IF
CASE
WHEN salesHeader.[Ship-to Name] IS NOT NULL THEN salesHeader.[Ship-to Name]
ELSE poHeader.[Ship-to Name]
END AS DeliveryName
Edit:
编辑：
Both COALESCE and ISNULL answers give in other answers are equally valid too for this particular case (and simpler, frankly)
其他答案中给出的 COALESCE 和 ISNULL 答案对于这种特殊情况也同样有效（坦率地说，更简单）
回答by lJohnson
Or use COALESCE...
或者使用 COALESCE ...
SELECT
COALESCE(salesHeader.[Ship-to Name], poHeader.[Ship-to Name]) AS DeliveryName
COALESCE will return the first non-NULL value in the list
COALESCE 将返回列表中的第一个非 NULL 值
回答by Tomek Szpakowicz
You can use CASE:
您可以使用案例：
(CASE WHEN cond-1 THEN value-if-1
WHEN cond-2 THEN value-if-2
ELSE default-value END) AS column-name
Or in this situation you can use COALESCE or ISNULL:
或者在这种情况下，您可以使用 COALESCE 或 ISNULL：
COALESCE(salesHeader.[Ship-to Name], poHeader.[Ship-to Name]) AS DeliveryName
ISNULL(salesHeader.[Ship-to Name], poHeader.[Ship-to Name]) AS DeliveryName
回答by priyanka.sarkar
As what gbn said, use CASE statement instead.
正如 gbn 所说，请改用 CASE 语句。
Since he has already done the coding, so I don't want to place the same thing again.
由于他已经完成了编码，所以我不想再放同样的东西。
Instead just giving you some site to refer for your understanding
而不是只是给你一些网站来参考你的理解
a) SQL SERVER – CASE Statement/Expression Examples and Explanation
a) SQL SERVER – CASE 语句/表达式示例和说明
b) CASE function in SQL Server 2005 - part I
b) SQL Server 2005 中的 CASE 函数 - 第一部分
c) CASE function in SQL Server 2005 - part II
c) SQL Server 2005 中的 CASE 函数 - 第二部分
回答by Seb
Not sure, but don't you have to put the 'AS DeliveryName' outsidethe IF?
不确定，但您不必将“AS DeliveryName”放在IF之外吗？
At least that's how it works in MySQL...
至少它在 MySQL 中是这样工作的......
