Case / If statements in SELECT Clause

This question already has an answer here:

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

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


    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
    

    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.

    链接地址: http://www.djcxy.com/p/94338.html

    上一篇: 如果在SQL Server中有条件,请选择值

    下一篇: 在SELECT子句中的Case / If语句