case when then else end 类是于switch...case... switch(0) { case 0: case 1: default: } 类是于(select时候) case 列名 when 0 then 。 case 列名 when 1 then 。要是单纯判断 就if begin 。 。 。 end else begin 。 。 。 end
CASE 函数用于计算多个条件并为每个条件返回单个值。CASE 函数通常的用途是将代码或缩写替换为可读性更强的值。下面的示例使用 CASE 函数更改产品系列类别的显示,以使这些类别更易理解。 复制代码 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
使用 CASE 处理条件数据 CASE 函数用于计算多个条件并为每个条件返回单个值。CASE 函数通常的用途是使用可读性更强的值替换代码或缩写。下面的查询使用 CASE 函数重命名书籍的分类,以使之更易理解。USE pubs SELECT 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 AS Category, CONVERT(varchar(30), title) AS "Shortened Title", price AS Price FROM titles WHERE price IS NOT NULL ORDER BY 1下面是结果集:category shortened title Price ------------------- ------------------------------ ------- Business Cooking with Computers: Surrep 11.95 Business Straight Talk About Computers 19.99 Business The Busy Executive's Database 19.99 Business You Can Combat Computer Stress 2.99 Modern Cooking Silicon Valley Gastronomic Tre 19.99 Modern Cooking The Gourmet Microwave 2.99 Popular Computing But Is It User Friendly? 22.95 Popular Computing Secrets of Silicon Valley 20.00 Psychology Computer Phobic AND Non-Phobic 21.59 Psychology Emotional Security: A New Algo 7.99 Psychology Is Anger the Enemy? 10.95 Psychology Life Without Fear 7.00 Psychology Prolonged Data Deprivation: Fo 19.99 Traditional Cooking Fifty Years in Buckingham Pala 11.95 Traditional Cooking Onions, Leeks, and Garlic: Coo 20.95 Traditional Cooking Sushi, Anyone? 14.99 (16 row(s) affected)CASE 函数的另一个用途给数据分类。下面的查询使用 CASE 函数对价格分类。SELECT 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 AS "Price Category", CONVERT(varchar(20), title) AS "Shortened Title" FROM pubs.dbo.titles ORDER BY price下面是结果集:Price Category Shortened Title --------------------- -------------------- Not yet priced The Psychology of Co Not yet priced Net Etiquette Very Reasonable Title You Can Combat Compu Very Reasonable Title The Gourmet Microwav 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 The Busy Executive's Coffee Table Title Straight Talk About Coffee Table Title Silicon Valley Gastr Coffee Table Title Prolonged Data Depri 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)
case ... when ... then ... else ... end
类是于switch...case... switch(0)
{
case 0:
case 1:
default:
}
类是于(select时候)
case 列名 when 0 then 。
case 列名 when 1 then 。要是单纯判断
就if
begin
。
。
。
end
else
begin
。
。
。
end
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
CASE 函数用于计算多个条件并为每个条件返回单个值。CASE 函数通常的用途是使用可读性更强的值替换代码或缩写。下面的查询使用 CASE 函数重命名书籍的分类,以使之更易理解。USE pubs
SELECT
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 AS Category,
CONVERT(varchar(30), title) AS "Shortened Title",
price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY 1下面是结果集:category shortened title Price
------------------- ------------------------------ -------
Business Cooking with Computers: Surrep 11.95
Business Straight Talk About Computers 19.99
Business The Busy Executive's Database 19.99
Business You Can Combat Computer Stress 2.99
Modern Cooking Silicon Valley Gastronomic Tre 19.99
Modern Cooking The Gourmet Microwave 2.99
Popular Computing But Is It User Friendly? 22.95
Popular Computing Secrets of Silicon Valley 20.00
Psychology Computer Phobic AND Non-Phobic 21.59
Psychology Emotional Security: A New Algo 7.99
Psychology Is Anger the Enemy? 10.95
Psychology Life Without Fear 7.00
Psychology Prolonged Data Deprivation: Fo 19.99
Traditional Cooking Fifty Years in Buckingham Pala 11.95
Traditional Cooking Onions, Leeks, and Garlic: Coo 20.95
Traditional Cooking Sushi, Anyone? 14.99 (16 row(s) affected)CASE 函数的另一个用途给数据分类。下面的查询使用 CASE 函数对价格分类。SELECT
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 AS "Price Category",
CONVERT(varchar(20), title) AS "Shortened Title"
FROM pubs.dbo.titles
ORDER BY price下面是结果集:Price Category Shortened Title
--------------------- --------------------
Not yet priced The Psychology of Co
Not yet priced Net Etiquette
Very Reasonable Title You Can Combat Compu
Very Reasonable Title The Gourmet Microwav
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 The Busy Executive's
Coffee Table Title Straight Talk About
Coffee Table Title Silicon Valley Gastr
Coffee Table Title Prolonged Data Depri
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)