SQL 内联 if 语句类型问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/287976/
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 inline if statement type question
提问by Mr. Flibble
I'd like to make an (MS)SQL query that returns something like this:
我想做一个 (MS)SQL 查询，返回如下内容：
Col1 Col2 Col3
---- --------------------- ------
AAA 18.92 18.92
BBB 20.00 40.00
AAA 30.84 30.84
BBB 06.00 12.00
AAA 30.84 30.84
AAA 46.79 46.79
AAA 86.40 86.40
where Col3 is equal to Col2 when Col1 = AAA and Col3 is twice Col2 when Col1 = BBB. Can someone point me in the rigth direction please?
其中，当 Col1 = AAA 时，Col3 等于 Col2，而当 Col1 = BBB 时，Col3 是 Col2 的两倍。有人可以指出我正确的方向吗？
回答by Joel Coehoorn
You didn't mention what kind of database you're using. Here's something that will work in SQL Server:
您没有提到您使用的是哪种数据库。以下是适用于 SQL Server 的内容：
SELECT Col1, Col2,
CASE WHEN Col1='AAA' THEN Col2 WHEN Col1='BBB' THEN Col2*2 ELSE NULL END AS Col3
FROM ...
回答by Draugnar
You can also use the ISNULLor COALESCEfunctions like thus, should the values be null:
如果值为空，您也可以使用ISNULL或这样的COALESCE函数：
SELECT ISNULL(Col1, 'AAA') AS Col1,
ISNULL(Col2, 0) AS Col2,
CASE WHEN ISNULL(Col1, 'AAA') = 'BBB' THEN ISNULL(Col2, 0) * 2
ELSE ISNULL(Col2)
END AS Col3
FROM Tablename
回答by George Mastros
It depends on your flavor of SQL. Case/When works with SQL Server (and possibly others).
这取决于您的 SQL 风格。案例/何时适用于 SQL Server（可能还有其他）。
Select Col1, Col2,
Case When Col1 = 'AAA' Then Col2 Else Col2 * 2 End As Col3
From YourTable
回答by Steven A. Lowe
select *
from yourtable
where (Col3 = col2 AND Col1 = 'AAA') OR
(Col3 = (2*Col2) AND Col1='BBB')
回答by Pankaj Awasthi
Sometimes the values are null, so they can be handled like this:
有时值是null，因此可以像这样处理它们：
select Address1 + (case when Address2='' then '' else ', '+Address2 end) +
(case when Address3='' then '' else ', '+ Address3 end) as FullAddress from users
