这是MSSQL CASE语句的好方案吗？ [重复]

This question already has an answer here:

这个问题在这里已有答案:

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

如何在SQL SELECT中执行IF ... THEN? 23个答案

I'm trying to offload some work from the CF server to the SQL Server (2008).

我正在尝试将一些工作从CF服务器卸载到SQL Server(2008)。

I'm running a query and the statusID value that is returned corresponds to one of 4 colors (Green, Yellow, Orange, and Red).

我正在运行查询,返回的statusID值对应于4种颜色(绿色,黄色,橙色和红色)中的一种。

select id, statusID 
from table

If this is the ideal situation to use a case statement, is this correct?

如果这是使用case语句的理想情况,这是正确的吗?

select id,  
    case  
        when statusid in (1,20,24)  
            then 'red'
    END as xxxx) as yyyy, *
from TABLE

And if this is correct, what goes into xxxx and yyyy above?

如果这是正确的,上面的xxxx和yyyy会是什么?

1 个解决方案

#1

You're close with the syntax, although you'd only want a maximum of one AS to give the column a name, so you could have something like this (of course, I've dreamt up values to illustrate options):

你接近语法,尽管你最多只想要一个AS来给列命名,所以你可以有这样的东西(当然,我已经设想了值来说明选项):

SELECT      id,  
            CASE 
                WHEN statusid IN (1,20,24) THEN 'red'
                WHEN statusid IN (2,30,34) THEN 'yellow'
                WHEN statusid 8 THEN 'orange'
                WHEN statusid > 35 THEN 'green'
                ELSE 'unrecognised'
            END AS ColorName,
            statusid

FROM        dbo.table