SQL 如何在选择查询中添加 if-then-else 条件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21189613/
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
How to add if-then-else conditions in a select query?
提问by user2963075
I would like to apply the following conditions in a select query.
我想在select query.
IF (TYPE = MBUSA or SDSA or MDSA OR PC) and (FINAL_STATUS = ACTIVE OR EXECUTED)
then GET ID and Parent_ID
ELSEIF (TYPE = MSA) and (FINAL_STATUS = ACTIVE OR EXECUTED)
then GET ID---(USING the ID from the else if condition AS FILTER,
IF TYPE = (DSA OR BUSA) AND (FINAL_STATUS = ACTIVE OR EXECUTED)
then GET ID and Parent_ID)
Do I need to write a procedure?
我需要写一个程序吗？
Following is the database structure:
以下是数据库结构：
回答by talegna
The CASEstatement can be used as an equivalent of an IF.. THEN... ELSE...statement. Not quite sure how you are wanting to select them as your question is unclear so here's a couple of suggestions.
该CASE语句可用作语句的等效项IF.. THEN... ELSE...。不太确定您想如何选择它们，因为您的问题不清楚，所以这里有一些建议。
To select a single column with a string representation of the conditional formatting:
要选择具有条件格式的字符串表示形式的单列：
SELECT
CASE
WHEN (TYPE IN ('MBUSA','SDSA','MDSA','PC') AND (FINAL_STATUS IN ('ACTIVE','EXECUTED'))
THEN ID + ' ' + P_ID
WHEN ((TYPE = 'MSA') AND (FINAL_STATUS IN ('ACTIVE','EXECUTED'))
THEN ID
WHEN (TYPE IN ('DSA','BUSA') AND (FINAL_STATUS IN ('ACTIVE','EXECUTED'))
THEN ID + ' ' + P_ID
ELSE ''
END AS columnName
FROM
tableName
To select each into separate columns
将每个选择到单独的列中
SELECT
CASE
WHEN (TYPE IN ('MBUSA','SDSA','MDSA','PC') AND (FINAL_STATUS IN ('ACTIVE','EXECUTED'))
THEN ID
WHEN ((TYPE = 'MSA') AND (FINAL_STATUS IN ('ACTIVE','EXECUTED'))
THEN ID
WHEN (TYPE IN ('DSA','BUSA') AND (FINAL_STATUS IN ('ACTIVE','EXECUTED'))
THEN ID
ELSE ''
END AS columnOneName
CASE
WHEN (TYPE IN ('MBUSA','SDSA','MDSA','PC') AND (FINAL_STATUS IN ('ACTIVE','EXECUTED'))
THEN P_ID
WHEN ((TYPE = 'MSA') AND (FINAL_STATUS IN ('ACTIVE','EXECUTED'))
THEN NULL
WHEN (TYPE IN ('DSA','BUSA') AND (FINAL_STATUS IN ('ACTIVE','EXECUTED'))
THEN P_ID
ELSE ''
END AS columnTwoName
FROM
tableName
Though looking at the above (I've probably transcribed your logic incorrectly) there are three case statements when you could get away with 2:
尽管查看上述内容（我可能错误地转录了您的逻辑），但是当您可以摆脱 2 时，有 3 个 case 语句：
CASE
WHEN (TYPE IN ('MBUSA','SDSA','MDSA','PC','DSA','BUSA') AND (FINAL_STATUS IN ('ACTIVE','EXECUTED'))
THEN ID + ' ' + P_ID
WHEN ((TYPE = 'MSA') AND (FINAL_STATUS IN ('ACTIVE','EXECUTED'))
THEN ID
ELSE ''
END AS columnName
I know you've put a screenshot up now, but an SQL Fiddlewould probably be more helpful or more detail so that table names etc. can be easily
我知道你现在已经放了一张截图，但是SQL Fiddle可能会更有帮助或更详细，以便表名等可以很容易地
Useful Links:
有用的链接：
http://technet.microsoft.com/en-us/library/ms181765.aspx
回答by A B
You can use CASE to implement IF-THEN-ELSE in PL/SQL - this is just an example:
您可以使用 CASE 在 PL/SQL 中实现 IF-THEN-ELSE - 这只是一个示例：
select case field1 WHEN '1' THEN 'VALUE1'
WHEN '2' THEN 'VALUE2'
ELSE 'VALUEOTHER'
END,
case field2 WHEN '1' THEN 'VALUE1'
WHEN '2' THEN 'VALUE2'
ELSE 'VALUEOTHER'
END
from (
select '1' "FIELD1",
'2' "FIELD2"
from dual
);
You cannot use the CASE statement on multiple columns; however, if you need to compare multiple fields in the same CASE statement, you could use a workaround like this:
不能在多列上使用 CASE 语句；但是，如果您需要比较同一个 CASE 语句中的多个字段，则可以使用如下解决方法：
select case field1 || '_' || field2
WHEN '1_1' THEN 'FIELD1-IS-VALUE1 AND FIELD2-IS-VALUE1'
WHEN '1_2' THEN 'FIELD1-IS-VALUE1 AND FIELD2-IS-VALUE2'
WHEN '2_1' THEN 'FIELD1-IS-VALUE2 AND FIELD2-IS-VALUE1'
WHEN '2_2' THEN 'FIELD1-IS-VALUE2 AND FIELD2-IS-VALUE2'
ELSE 'VALUEOTHER'
END
from (
select '1' "FIELD1",
'2' "FIELD2"
from dual
);
If the SQL statement becomes difficult to read it may be better to use views with the SQL statement or use PL/SQL.
如果 SQL 语句变得难以阅读，最好使用 SQL 语句的视图或使用 PL/SQL。
References:
参考：
