SQL 如果其他列为空，则选择一列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9878523/
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
Select a column if other column is null
提问by Ronald McDonald
I need to select a field called ProgramID from a table and if the ProgramID is NULL then I need to select the value in the InterimProgramID from the same table and alias it as ProgramID.
我需要从表中选择一个名为 ProgramID 的字段，如果 ProgramID 为 NULL，那么我需要从同一个表中选择 InterimProgramID 中的值并将其别名为 ProgramID。
How can I make a conditional SELECT statement to do this?
我怎样才能做一个有条件的 SELECT 语句来做到这一点？
回答by Peter Aron Zentai
You need the ISNULL function.
您需要 ISNULL 函数。
SELECT ISNULL(a, b)
bgets selected if ais null.
b如果a为空则被选中。
Also, you can use the WHEN/THEN select option, lookup in BOL. Essentially: its c switch/case block meets SQL.
此外，您可以使用 WHEN/THEN 选择选项，在 BOL 中查找。本质上：它的 c switch/case 块符合 SQL。
回答by Justin Pihony
You can use either the ISNULLfunction or the COALESCEfunction. They both do pretty much the same thing, however ISNULLonly takes two parameters and COALESCEtakes multiple parameters (returning the first non-null it encounters). Both try the first param, then the second, (and COALESCE continues on)
您可以使用ISNULL函数或COALESCE函数。它们都做几乎相同的事情，但是ISNULL只接受两个参数和COALESCE多个参数（返回它遇到的第一个非空值）。都尝试第一个参数，然后是第二个，（并且 COALESCE 继续）
DECLARE @IAMNULL VARCHAR
DECLARE @IAMNOTNULL VARCHAR
SET @IAMNOTNULL = 'NOT NULL'
SELECT ISNULL(@IAMNULL, @IAMNOTNULL)
--Output: 'NOT NULL'
DECLARE @IAMNULLALSO VARCHAR
SELECT COALESCE(@IAMNULL, @IAMNULLALSO, @IAMNOTNULL)
--Output: 'NOT NULL'
回答by paparazzo
select COALESCE ( ProgramID , InterimProgramID ) as 'ProgramID'
回答by onedaywhen
SELECT ProgramID
FROM a_table
WHERE ProgramID IS NOT NULL
UNION
SELECT InterimProgramID AS ProgramID
FROM a_table
WHERE ProgramID IS NULL;
回答by Boi G
There is also:
还有：
Select NVL(Column_A, Column_B) From 'schema'.'table_name'
The NVL( ) function is available in Oracle, and not in MySQL or SQL Server. This function is used to replace NULL value with another value. It is similar to the IFNULL Function in MySQL and the ISNULL Function in SQL Server.
NVL( ) 函数在 Oracle 中可用，在 MySQL 或 SQL Server 中不可用。此函数用于用另一个值替换 NULL 值。它类似于 MySQL 中的 IFNULL 函数和 SQL Server 中的 ISNULL 函数。
回答by sathish m
You can also use IFNULL function
您还可以使用 IFNULL 函数
select IFNULL(ProgramId,interimId) as ProgramId
回答by user220934
Coalesce('zzz-' + ProgramID, InterimID) as programID will still ignore ProgramID even if you have a pretext value. It's a cool little function
Coalesce('zzz-' + ProgramID, InterimID) as programID 仍然会忽略 ProgramID，即使您有一个借口值。这是一个很酷的小功能
