|
1. CASE 介绍 计算条件列表，并返回多个可能的结果表达式之一。 CASE 表达式有两种格式： ☆ CASE 简单表达式:它通过将表达式与一组简单的表达式进行比较来确定结果。 ☆ CASE 搜索表达式:它通过计算一组布尔表达式来确定结果。 这两种格式都支持可选的 ELSE 参数。 CASE 可用于允许使用有效表达式的任意语句或子句。 例如，可以在 SELECT、UPDATE、DELETE 和 SET 等语句以及 select_list、IN、WHERE、ORDER BY 和 HAVING 等子句中使用 CASE。
2. 示例分析2.1 准备数据-- ---------------------------- -- Table structure for zsh_170225 -- ---------------------------- DROP TABLE [dbo].[zsh_170225] GO CREATE TABLE [dbo].[zsh_170225] ( [name] varchar(255) NULL , [sex] varchar(255) NULL ) GO -- ---------------------------- -- Records of zsh_170225 -- ---------------------------- INSERT INTO [dbo].[zsh_170225] ([name], [sex]) VALUES (N'张三', N'1') GO GO INSERT INTO [dbo].[zsh_170225] ([name], [sex]) VALUES (N'李四', N'2') GO GO INSERT INTO [dbo].[zsh_170225] ([name], [sex]) VALUES (N'王五', null) GO GO INSERT INTO [dbo].[zsh_170225] ([name], [sex]) VALUES (N'赵六', N'1') GO GO
2.2 演示 sql_1简单 CASE 表达式︰CASE 简单表达式的工作方式如下：将第一个表达式与每个 WHEN 子句中的表达式进行比较，以确定它们是否等效。 如果这些表达式等效，将返回 THEN 子句中的表达式。 Select name,sex, CASE sex WHEN '1' THEN '男' --如果sex字段值为1就sex=’男’ WHEN '2' THEN '女' --如果sex字段值为2就sex=’女’ ELSE '其他' END --否则sex=’其他’ AS sex_n FROM zsh_170225 ![]()
2.3 演示 sql_2搜索 CASE 表达式︰计算结果，按顺序指定，对比每个 WHEN 子句。 SELECT name, sex, CASE WHEN sex = '1' THEN '男' --如果sex字段值为1就sex=’男’ WHEN sex = '2' THEN '女' --如果sex字段值为2就sex=’女’ ELSE '其他' END --否则sex=’其他’ AS sex_n FROM zsh_170225
![]()
这两种方式，可以实现相同的功能。 简单 CASE 函数的写法相对比较简洁，但是和 CASE 搜索函数相比，功能方面会有些限制，比如写判断式。 还有一个需要注意的问题，CASE 函数只返回第一个符合条件的值，剩下的CASE 部分将会被自动忽略。 --比如说，下面这段 SQL，你永远无法得到“K2”这个结果 CASE WHEN col_1 IN ( 'a', 'b') THEN 'K1' WHEN col_1 IN ('a') THEN 'K2' ELSE'其他' END
3. CASE 主流用法介绍:1）使用带有 CASE 简单表达式的 SELECT 语句 在 SELECT 语句中，CASE 简单表达式只能用于等同性检查，而不进行其他比较。 下面的示例使用 CASE 表达式更改产品系列类别的显示，以使这些类别更易于理解。 SELECT ProductNumber, Category = CASE ProductLine WHEN 'R' THEN 'Road' -- 当ProductLine =’R’ 时, Category=’ Road’ WHEN 'M' THEN 'Mountain' -- 当ProductLine =’M’ 时, Category='Mountain' WHEN 'T' THEN 'Touring' -- 当ProductLine =’T’ 时, Category='Touring' WHEN 'S' THEN 'Other sale items' -- 当ProductLine =’S’ 时, Category=' Other sale items ' ELSE 'Not for sale' --否则Category=' Not for sale ' END, Name FROM Production.Product ORDER BY ProductNumber;
2）使用带有 CASE 搜索表达式的 SELECT 语句 在 SELECT 语句中，CASE 搜索表达式允许根据比较值替换结果集中的值。 下面的示例根据产品的价格范围将标价显示为文本注释。 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 ; 3）在 ORDER BY 子句中使用 CASE 在 ORDER BY 子句中使用 CASE 表达式，以根据给定的列值确定行的排序顺序。 在第一个示例中，会计算 SalariedFlag 表中 HumanResources.Employee 列的值。 SalariedFlag 设置为 1 的员工将按 BusinessEntityID 以降序顺序返回。 SalariedFlag 设置为 0 的员工将按 BusinessEntityID 以升序顺序返回。 在第二个示例中，当 TerritoryName 列等于“United States”时，结果集会按 CountryRegionName 列排序，对于所有其他行则按 CountryRegionName 排序。 SELECT BusinessEntityID, SalariedFlag FROM HumanResources.Employee ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID END DESC ,CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END; --如果SalariedFlag=1时按照BusinessEntityID字段的降序排 --如果SalariedFlag=0时按照BusinessEntityID字段的升序排
SELECT BusinessEntityID, LastName, TerritoryName, CountryRegionName FROM Sales.vSalesPerson WHERE TerritoryName IS NOT NULL ORDER BY CASE CountryRegionName WHEN 'United States' THEN TerritoryName ELSE CountryRegionName END; -- 当CountryRegionName= 'United States'时, 按TerritoryName字段的升序排,否则就按照CountryRegionName字段的升序排SELECT BusinessEntityID, LastName, TerritoryName, CountryRegionName FROM Sales.vSalesPerson WHERE TerritoryName IS NOT NULL ORDER BY CASE CountryRegionName WHEN 'United States' THEN TerritoryName ELSE CountryRegionName END; -- 当CountryRegionName= 'United States'时, 按TerritoryName字段的升序排,否则就按照CountryRegionName字段的升序排
4）在 UPDATE 语句中使用 CASE
在 UPDATE 语句中使用 CASE 表达式，以确定为 VacationHours 设置为 0 的员工的 SalariedFlag 列所设置的值。 如果 VacationHours 减去 10 小时后会得到一个负值，则 VacationHours 将增加 40 小时；否则 VacationHours 将增加 20 小时。 OUTPUT 子句用于显示前后的休假时间值。 UPDATE HumanResources.Employee SET VacationHours = ( CASE WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40 ELSE (VacationHours + 20.00) --如果VacationHours - 10.00) < 0时, VacationHours= VacationHours + 40否则VacationHours = VacationHours + 20.00 END ) OUTPUT Deleted.BusinessEntityID, Deleted.VacationHours AS BeforeValue, Inserted.VacationHours AS AfterValue WHERE SalariedFlag = 0;
5）在 HAVING 子句中使用 CASE
下面的示例在 HAVING 子句中使用 CASE 表达式，以限制由 SELECT 语句返回的行。 该语句返回为每个作业标题中的最大每小时速率 HumanResources.Employee 表。 HAVING 子句将职位限制为两类员工：一是最高每小时薪金超过 40 美元的男性员工，二是最高每小时薪金超过 42 美元的女性员工。 SELECT JobTitle, MAX(ph1.Rate)AS MaximumRate FROM HumanResources.Employee AS e JOIN HumanResources.EmployeePayHistory AS ph1 ON e.BusinessEntityID = ph1.BusinessEntityID GROUP BY JobTitle HAVING (MAX(CASE WHEN Gender = 'M' THEN ph1.Rate ELSE NULL END) > 40.00 -- Gender = 'M'时, ph1.Rate字段,否则null OR MAX(CASE WHEN Gender = 'F' THEN ph1.Rate ELSE NULL END) > 42.00) ---- Gender = 'F'时, ph1.Rate字段,否则null ORDER BY MaximumRate DESC; | 
