end

as maxnu

from test_t
例如，下面的句子顯示中文年月。

select getdate() as 日期,case month(getdate())
when 11 then '十一'
when 12 then '十二'
else substring('1234567890'， month(getdate()),1)
end '月' as 月份

=================================================

CASE可能是 SQL 其中一個(gè)被誤用最多的關(guān)鍵字。雖然你可能以前用過這個(gè)關(guān)鍵詞來建立一個(gè)字段，但它有更多的用法。例如，你可以在WHERE子句中使用CASE。

先來看看CASE的語法。它的語法在一般的SELECT中如下：

SELECT=
CASE
WHENTHEN
WHENTHEN
ELSE
END

在上述代碼中，尖括號中的內(nèi)容需要用具體的參數(shù)值來代替。以下是一個(gè)典型的例子：

USEpubs
GO
SELECT
Title,
'Price Range'=
CASE
WHENpriceISNULLTHEN'Unpriced'
WHENpriceTHEN'10Bargain'<10THEN'Bargain'
WHENpriceBETWEN10and20THEN'Average'
ELSE'Gift to impress relatives'
END
FROMtitles
ORDERBYprice
GO

這種方法在CASE中很常見，但使用CASE實(shí)際上可以做得更多。例如，下面的GROUPBY句子中的CASE：

SELECT'Number of Titles',Count(*)
FROMtitles
GROUPBY
CASE
WHENpriceISNULLTHEN'Unpriced'
WHENpriceTHEN'10Bargain'<10THEN'Bargain'
WHENpriceBETWEN10and20THEN'Average'
ELSE'Gift to impress relatives'
END
GO

您甚至可以將這些選項(xiàng)與ORDERBY子句相結(jié)合，如下所示：

USEpubs
GO
SELECT
CASE
WHENpriceISNULLTHEN'Unpriced'
WHENpriceTHEN'10Bargain'<10THEN'Bargain'
WHENpriceBETWEN10and20THEN'Average'
ELSE'Gift to impress relatives'
ENDASRange,
Title
FROMtitles
GROUPBY
CASE
WHENpriceISNULLTHEN'Unpriced'
WHENpriceTHEN'10Bargain'<10THEN'Bargain'
WHENpriceBETWEN10and20THEN'Average'
ELSE'Gift to impress relatives'
END,
Title
ORDERBY
CASE
WHENpriceISNULLTHEN'Unpriced'
WHENpriceTHEN'10Bargain'<10THEN'Bargain'
WHENpriceBETWEN10and20THEN'Average'
ELSE'Gift to impress relatives'
END,
Title
GO

請注意，如果您想在GROUPBY塊中應(yīng)用CASE，則需要在GROUPBY塊中重復(fù)SELECT塊中的CASE。