SQL Server 2008 - SELECT 子句中的 Case / If 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1122557/
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 2008 - Case / If statements in SELECT Clause
提问by Tejaswi Yerukalapudi
I have a Query that's supposed to run like this -
我有一个应该像这样运行的查询 -
If(var = xyz) SELECT col1, col2 ELSE IF(var = zyx) SELECT col2, col3 ELSE SELECT col7,col8 FROM . . .
How do I achieve this in T-SQL without writing separate queries for each clause? Currently I'm running it as
如何在 T-SQL 中实现这一点而不为每个子句编写单独的查询？目前我正在运行它
IF (var = xyz) {
Query1
}
ELSE IF (var = zyx) {
Query2
}
ELSE {
Query3
}
That's just a lot of redundant code just to select different columns depending on a value. Any alternatives?
这只是很多冗余代码，只是为了根据值选择不同的列。任何替代方案？
回答by Sam Saffron
You are looking for the CASE statement
您正在寻找 CASE 语句
http://msdn.microsoft.com/en-us/library/ms181765.aspx
http://msdn.microsoft.com/en-us/library/ms181765.aspx
Example copied from MSDN:
从 MSDN 复制的示例：
USE AdventureWorks;
GO
SELECT ProductNumber, Category =
CASE ProductLine
WHEN 'R' THEN 'Road'
WHEN 'M' THEN 'Mountain'
WHEN 'T' THEN 'Touring'
WHEN 'S' THEN 'Other sale items'
ELSE 'Not for sale'
END,
Name
FROM Production.Product
ORDER BY ProductNumber;
GO
回答by Remus Rusanu
Just a note here that you may actually be better off having 3 separate SELECTS for reasons of optimization. If you have one single SELECT then the generated plan will have to project all columns col1, col2, col3, col7, col8 etc, although, depending on the value of the runtime @var, only some are needed. This may result in plans that do unnecessary clustered index lookups because the non-clustered index Doesn't cover all columns projected by the SELECT.
请注意，出于优化的原因，您实际上最好拥有 3 个单独的 SELECTS。如果您只有一个 SELECT，那么生成的计划将必须投影所有列 col1、col2、col3、col7、col8 等，尽管根据运行时 @var 的值，只需要一些列。这可能会导致计划执行不必要的聚集索引查找，因为非聚集索引不涵盖 SELECT 投影的所有列。
On the other hand 3 separate SELECTS, each projecting the needed columns only may benefit from non-clustered indexes that cover just your projected column in each case.
另一方面，3 个单独的 SELECTS，每个仅投影所需的列可能会受益于在每种情况下仅覆盖您的投影列的非聚集索引。
Of course this depends on the actual schema of your data model and the exact queries, but this is just a heads up so you don't bring the imperative thinking mind frame of procedural programming to the declarative world of SQL.
当然，这取决于您的数据模型的实际模式和确切的查询，但这只是一个提示，因此您不会将过程编程的命令式思维框架带入 SQL 的声明性世界。
回答by Jeffrey Hantin
Try something like
尝试类似的东西
SELECT
CASE var
WHEN xyz THEN col1
WHEN zyx THEN col2
ELSE col7
END AS col1,
...
In other words, use a conditional expression to select the value, then rename the column.
换句话说，使用条件表达式选择值，然后重命名列。
Alternately, you could build up some sort of dynamic SQL hack to share the query tail; I've done this with iBatis before.
或者，您可以构建某种动态 SQL hack 来共享查询尾部；我以前用 iBatis 做过这个。
回答by Sutirth
Simple CASE expression:
简单的 CASE 表达式：
CASE input_expression
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END
Searched CASE expression:
搜索 CASE 表达式：
CASE
WHEN Boolean_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END
Reference: http://msdn.microsoft.com/en-us/library/ms181765.aspx
回答by Rob Boek
CASE is the answer, but you will need to have a separate case statement for each column you want returned. As long as the WHERE clause is the same, there won't be much benefit separating it out into multiple queries.
CASE 是答案，但您需要为要返回的每一列都有一个单独的 case 语句。只要 WHERE 子句相同，将其分成多个查询就不会有太大的好处。
Example:
例子：
SELECT
CASE @var
WHEN 'xyz' THEN col1
WHEN 'zyx' THEN col2
ELSE col7
END,
CASE @var
WHEN 'xyz' THEN col2
WHEN 'zyx' THEN col3
ELSE col8
END
FROM Table
...
回答by Joel Mansford
The most obvious solutions are already listed. Depending on where the query is sat (i.e. in application code) you can't always use IF statements and the inline CASE statements can get painful where lots of columns become conditional. Assuming Col1 + Col3 + Col7 are the same type, and likewise Col2, Col4 + Col8 you can do this:
最明显的解决方案已经列出。根据查询所在的位置（即在应用程序代码中），您不能总是使用 IF 语句，并且内联 CASE 语句在许多列成为条件时会变得很痛苦。假设 Col1 + Col3 + Col7 是相同的类型，同样的 Col2, Col4 + Col8 你可以这样做：
SELECT Col1, Col2 FROM tbl WHERE @Var LIKE 'xyz'
UNION ALL
SELECT Col3, Col4 FROM tbl WHERE @Var LIKE 'zyx'
UNION ALL
SELECT Col7, Col8 FROM tbl WHERE @Var NOT LIKE 'xyz' AND @Var NOT LIKE 'zyx'
As this is a single command there are several performance benefits with regard to plan caching. Also the Query Optimiser will quickly eliminate those statements where @Var doesn't match the appropriate value without touching the storage engine.
由于这是一个单一的命令，因此在计划缓存方面有几个性能优势。此外，查询优化器将快速消除那些 @Var 与适当值不匹配的语句，而不会触及存储引擎。
