SQL 如果字段为空，则拉取某些字段；否则，拉其他字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10214554/
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
If field is null, pull certain fields; otherwise, pull other fields
提问by Sesame
I have the following table:
我有下表：
TestTable
ColumnA
Column1
Column2
Column3
Column4
I want to write a simple SQL statement that checks ColumnA and depending on whether it is null or not returns certain columns.
我想编写一个简单的 SQL 语句来检查 ColumnA 并根据它是否为空返回某些列。
Something like (pseudo):
类似（伪）：
If ColumnA Is Null
SELECT ColumnA, Column1, Column2
FROM TestTable
Else
SELECT ColumnA, Column3, Column4
FROM TestTable
Any ideas?
有任何想法吗？
回答by Olivier Jacot-Descombes
Use SQL CASE expressions:
使用 SQL CASE 表达式：
SELECT
ColumnA,
CASE WHEN ColumnA IS NULL THEN Column1 ELSE Column3 END AS ColumnB,
CASE WHEN ColumnA IS NULL THEN Column2 ELSE Column4 END AS ColumnC
FROM
TestTable
回答by MatBailie
I can think of a couple of routes, none are necessarily pretty...
我能想到几条路线，没有一条路线一定很漂亮......
People often find the first one, then look for something better. I'm not sure that there really is anything better.
人们通常会先找到第一个，然后再寻找更好的。我不确定是否真的有更好的东西。
SELECT
ColumnA,
CASE WHEN ColumnA IS NULL THEN Column1 ELSE Column2 END AS ColumnB,
CASE WHEN ColumnA IS NULL THEN Column3 ELSE Column4 END AS ColumnC
FROM
yourTable
Or...
或者...
SELECT
yourTable.ColumnA,
subTable.ColumnB,
subTable.ColumnC
FROM
yourTable
CROSS APPLY
(
SELECT yourTable.Column1 AS ColumnB, yourTable.Column3 AS ColumnC WHERE yourTable.ColumnA IS NULL
UNION ALL
SELECT yourTable.Column2 AS ColumnB, yourTable.Column4 AS ColumnC WHERE yourTable.ColumnA IS NOT NULL
)
AS subTable
Or...
或者...
SELECT
ColumnA,
Column1 AS ColumnB,
Column2 AS ColumnC
FROM
yourTable
WHERE
ColumnA IS NULL
UNION ALL
SELECT
ColumnA,
Column2 AS ColumnB,
Column4 AS ColumnC
FROM
yourTable
WHERE
ColumnA IS NOT NULL
回答by Bob Jarvis - Reinstate Monica
One more way to skin this particular cat:
给这只猫剥皮的另一种方法：
SELECT ColumnA,
NVL2(ColumnA, Column3, Column1),
NVL2(ColumnA, Column4, Column2)
FROM TestTable
Share and enjoy.
分享和享受。
