数列求和公式Sqlrver中的CASE语句CASE 具有两种格式:
变化无常英语
Simple CASE function: CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[服装设计师工资
ELSE el_result_expression
]
非诚勿扰2 见与不见END
heldSearched CASE function:
CASE
cmbcWHEN Boolean_expression THEN result_expression
[ ...n ]
[
ELSE el_result_expression
]
END
A. 使⽤带有简单 CASE 函数的 SELECT 语句
在 SELECT 语句中，简单 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
cantoneORDER BY ProductNumber;
GO
B. 使⽤带有简单 CASE 函数和 CASE 搜索函数的 SELECT 语句
在 SELECT 语句中，CASE 搜索函数允许根据⽐较值在结果集内对值进⾏替换。下⾯的⽰例根据产品的价格范围将标价显⽰为⽂本注释。
USE AdventureWorks;
GO
SELECT ProductNumber, Name, 'Price Range' =
CASE
WHEN ListPrice = 0 THEN 'Mfg item - not for resale'地道口语
WHEN ListPrice < 50 THEN 'Under $50'
WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'
WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'
ELSE 'Over $1000'
END
FROM Production.Product
ORDER BY ProductNumber ;
GO
以上内容来⾃Sql rver 2005帮助⽂档
其他Ca语句实例:
USE pubs
GO
SELECT
Title,
'Price Range' =
CASE
WHEN price IS NULL THEN 'Unpriced'
WHEN price < 10 THEN 'Bargain'
WHEN price BETWEEN 10 and 20 THEN 'Average'rnai
ELSE 'Gift to impress relatives'
END
FROM titles
ORDER BY price
GO
这是 CASE 的典型⽤法，但是使⽤ CASE 其实可以做更多的事情。⽐⽅说下⾯的 GROUP BY ⼦句中的 CASE：
SELECT 'Number of Titles', Count(*)
FROM titles
GROUP BY
202012月四级答案CASE
WHEN price IS NULL THEN 'Unpriced'
WHEN price < 10 THEN 'Bargain'
WHEN price BETWEEN 10 and 20 THEN 'Average'
ELSE 'Gift to impress relatives'
END
GO
你甚⾄还可以组合这些选项，添加⼀个 ORDER BY ⼦句，如下所⽰：
USE pubs
GO
SELECT
CASE
WHEN price IS NULL THEN 'Unpriced'
WHEN price < 10 THEN 'Bargain'
WHEN price BETWEEN 10 and 20 THEN 'Average'
ELSE 'Gift to impress relatives'
END AS Range,
Title
FROM titles
GROUP BY
CASE
WHEN price IS NULL THEN 'Unpriced'
WHEN price < 10 THEN 'Bargain'
WHEN price BETWEEN 10 and 20 THEN 'Average'
ELSE 'Gift to impress relatives'
END,
Title
ORDER BY
CASE
WHEN price IS NULL THEN 'Unpriced'
WHEN price < 10 THEN 'Bargain'
WHEN price BETWEEN 10 and 20 THEN 'Average'
ELSE 'Gift to impress relatives'
END,
Title
GO
注意，为了在 GROUP BY 块中使⽤ CASE，查询语句需要在 GROUP BY 块中重复 SELECT 块中的 CASE 块。
注意:Simple CASE Function不能⽤来判断null值.
如果 ca 表达式1 null then 0 el 表达式2, 它永远是执⾏表达式2，即使表达式1的值是null
应该这样写: ca when 表达式1 is null then 0 el 表达式,
