SQL Server 2008 - SELECT子句中的Case / If语句[duplicate]

This question already has an answer here:

这个问题已经有了答案:

How do I perform an IF…THEN in an SQL SELECT? 22 answers
如何在SQL SELECT中执行IF…22日答案

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中实现这一点，而不为每个子句编写单独的查询?目前我运行的是as


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?

这只是很多冗余代码，只是为了根据值选择不同的列。替代品吗?

6 个解决方案

#1

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.

这里需要注意的是，由于优化的原因，最好有三个单独的选择。如果您只有一个选择，那么生成的计划将不得不投射所有列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个单独的选择，每个投射所需的列可能只受益于在每种情况下只覆盖投影列的非聚集索引。

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的声明式世界中。

#2

You are looking for the CASE statement

你在寻找案例陈述

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

#3

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这样做过。

#4

Simple CASE expression:

简单的案例表达:

CASE input_expression 
     WHEN when_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END

Searched CASE expression:

搜索案例表达:

CASE
     WHEN Boolean_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END

Reference: http://msdn.microsoft.com/en-us/library/ms181765.aspx

参考:http://msdn.microsoft.com/en-us/library/ms181765.aspx

#5

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
...

#6

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语句和内联情况语句，因为很多列都变成了条件语句。假设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.

由于这是一个命令，所以计划缓存有几个性能优势。此外，查询Optimiser将快速删除那些@Var不匹配适当值而不触及存储引擎的语句。

#1