SQL_case 语法when

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 其中一个被误用最多的关键字。虽然你可能以前用过这个关键词来建立一个字段，但它有更多的用法。例如，你可以在WHERE子句中使用CASE。

先来看看CASE的语法。它的语法在一般的SELECT中如下：

SELECT=
CASE
WHENTHEN
WHENTHEN
ELSE
END

在上述代码中，尖括号中的内容需要用具体的参数值来代替。以下是一个典型的例子：

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实际上可以做得更多。例如，下面的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

您甚至可以将这些选项与ORDERBY子句相结合，如下所示：

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块中应用CASE，则需要在GROUPBY块中重复SELECT块中的CASE。