示例
A. 使用带有简单 CASE 函数的 SELECT 语句
在 SELECT 语句中,简单 CASE 函数仅检查是否相等,而不进行其它比较。下面的示例使用 CASE 函数更改图书分类显示,以使其更易于理解。USE pubs
GO
SELECT Category =
CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'mod_cook' THEN 'Modern Cooking'
WHEN 'business' THEN 'Business'
WHEN 'psychology' THEN 'Psychology'
WHEN 'trad_cook' THEN 'Traditional Cooking'
ELSE 'Not yet categorized'
END,
CAST(title AS varchar(25)) AS 'Shortened Title',
price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY type, price
COMPUTE AVG(price) BY type
GO下面是结果集:Category Shortened Title Price
------------------- ------------------------- --------------------------
Business You Can Combat Computer S 2.99
Business Cooking with Computers: S 11.95
Business The Busy Executive's Data 19.99
Business Straight Talk About Compu 19.99 avg
==========================
13.73 Category Shortened Title Price
------------------- ------------------------- --------------------------
Modern Cooking The Gourmet Microwave 2.99
Modern Cooking Silicon Valley Gastronomi 19.99 avg
==========================
11.49 Category Shortened Title Price
------------------- ------------------------- --------------------------
Popular Computing Secrets of Silicon Valley 20.00
Popular Computing But Is It User Friendly? 22.95 avg
==========================
21.48 Category Shortened Title Price
------------------- ------------------------- --------------------------
Psychology Life Without Fear 7.00
Psychology Emotional Security: A New 7.99
Psychology Is Anger the Enemy? 10.95
Psychology Prolonged Data Deprivatio 19.99
Psychology Computer Phobic AND Non-P 21.59 avg
==========================
13.50 Category Shortened Title Price
------------------- ------------------------- --------------------------
Traditional Cooking Fifty Years in Buckingham 11.95
Traditional Cooking Sushi, Anyone? 14.99
Traditional Cooking Onions, Leeks, and Garlic 20.95 avg
==========================
15.96 (21 row(s) affected)B. 使用带有简单 CASE 函数和 CASE 搜索函数的 SELECT 语句
在 SELECT 语句中,CASE 搜索函数允许根据比较值在结果集内对值进行替换。下面的示例根据图书的价格范围将价格(money 列)显示为文本注释。USE pubs
GO
SELECT 'Price Category' =
CASE
WHEN price IS NULL THEN 'Not yet priced'
WHEN price < 10 THEN 'Very Reasonable Title'
WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
ELSE 'Expensive book!'
END,
CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
ORDER BY price
GO下面是结果集:Price Category Shortened Title
--------------------- --------------------
Not yet priced Net Etiquette
Not yet priced The Psychology of Co
Very Reasonable Title The Gourmet Microwav
Very Reasonable Title You Can Combat Compu
Very Reasonable Title Life Without Fear
Very Reasonable Title Emotional Security:
Coffee Table Title Is Anger the Enemy?
Coffee Table Title Cooking with Compute
Coffee Table Title Fifty Years in Bucki
Coffee Table Title Sushi, Anyone?
Coffee Table Title Prolonged Data Depri
Coffee Table Title Silicon Valley Gastr
Coffee Table Title Straight Talk About
Coffee Table Title The Busy Executive's
Expensive book! Secrets of Silicon V
Expensive book! Onions, Leeks, and G
Expensive book! Computer Phobic And
Expensive book! But Is It User Frien (18 row(s) affected)
A. 使用带有简单 CASE 函数的 SELECT 语句
在 SELECT 语句中,简单 CASE 函数仅检查是否相等,而不进行其它比较。下面的示例使用 CASE 函数更改图书分类显示,以使其更易于理解。USE pubs
GO
SELECT Category =
CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'mod_cook' THEN 'Modern Cooking'
WHEN 'business' THEN 'Business'
WHEN 'psychology' THEN 'Psychology'
WHEN 'trad_cook' THEN 'Traditional Cooking'
ELSE 'Not yet categorized'
END,
CAST(title AS varchar(25)) AS 'Shortened Title',
price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY type, price
COMPUTE AVG(price) BY type
GO下面是结果集:Category Shortened Title Price
------------------- ------------------------- --------------------------
Business You Can Combat Computer S 2.99
Business Cooking with Computers: S 11.95
Business The Busy Executive's Data 19.99
Business Straight Talk About Compu 19.99 avg
==========================
13.73 Category Shortened Title Price
------------------- ------------------------- --------------------------
Modern Cooking The Gourmet Microwave 2.99
Modern Cooking Silicon Valley Gastronomi 19.99 avg
==========================
11.49 Category Shortened Title Price
------------------- ------------------------- --------------------------
Popular Computing Secrets of Silicon Valley 20.00
Popular Computing But Is It User Friendly? 22.95 avg
==========================
21.48 Category Shortened Title Price
------------------- ------------------------- --------------------------
Psychology Life Without Fear 7.00
Psychology Emotional Security: A New 7.99
Psychology Is Anger the Enemy? 10.95
Psychology Prolonged Data Deprivatio 19.99
Psychology Computer Phobic AND Non-P 21.59 avg
==========================
13.50 Category Shortened Title Price
------------------- ------------------------- --------------------------
Traditional Cooking Fifty Years in Buckingham 11.95
Traditional Cooking Sushi, Anyone? 14.99
Traditional Cooking Onions, Leeks, and Garlic 20.95 avg
==========================
15.96 (21 row(s) affected)B. 使用带有简单 CASE 函数和 CASE 搜索函数的 SELECT 语句
在 SELECT 语句中,CASE 搜索函数允许根据比较值在结果集内对值进行替换。下面的示例根据图书的价格范围将价格(money 列)显示为文本注释。USE pubs
GO
SELECT 'Price Category' =
CASE
WHEN price IS NULL THEN 'Not yet priced'
WHEN price < 10 THEN 'Very Reasonable Title'
WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
ELSE 'Expensive book!'
END,
CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
ORDER BY price
GO下面是结果集:Price Category Shortened Title
--------------------- --------------------
Not yet priced Net Etiquette
Not yet priced The Psychology of Co
Very Reasonable Title The Gourmet Microwav
Very Reasonable Title You Can Combat Compu
Very Reasonable Title Life Without Fear
Very Reasonable Title Emotional Security:
Coffee Table Title Is Anger the Enemy?
Coffee Table Title Cooking with Compute
Coffee Table Title Fifty Years in Bucki
Coffee Table Title Sushi, Anyone?
Coffee Table Title Prolonged Data Depri
Coffee Table Title Silicon Valley Gastr
Coffee Table Title Straight Talk About
Coffee Table Title The Busy Executive's
Expensive book! Secrets of Silicon V
Expensive book! Onions, Leeks, and G
Expensive book! Computer Phobic And
Expensive book! But Is It User Frien (18 row(s) affected)
例如,下面的语句显示中文年月select getdate() as 日期,case month(getdate())
when 11 then '十一'
when 12 then '十二'
else substring('一二三四五六七八九十', month(getdate()),1)
end+'月' as 月份
declare @b varchar
set @b = (case month(@a) when 1 then '一'
when 2 then '二'
when 3 then '三'
...
end)
declare @aa char(2)
declare @bb char(2)declare @ccc tinyint
declare @ddd tinyint
declare @eee tinyintif @aa = 'aa'
begin
if @bb = '1'
set @ccc = 1
if (@bb = '2' or @bb = '3')
set @ccc = 2
if @bb = '4'
set @ccc = 3
endif @aa = 'bb'
begin
if (@bb = '1' or @bb = '2')
set @ddd = 1
if @bb = '3'
set @ddd = 2
if @bb = '4'
set @ddd = 3
end
if @aa = 'cc'
begin
if (@bb = '1' or @bb = '2')
set @ddd = 1
if @bb = '3'
set @ddd = 2
if @bb = '4'
set @ddd = 3
end就是完成这样的功能,请各位大侠看看