SQL Server 2008 - Case / If statements in SELECT C

问题:

This question already has an answer here:

• How do I perform an IF…THEN in an SQL SELECT? 24 answers

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

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?

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

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.

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.

回答2:

You are looking for the CASE statement

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

Example copied from 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.

回答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

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

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:

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.