[转载]T-SQL查询进阶--基于列的逻辑表达式

[转载]T-SQL查询进阶–基于列的逻辑表达式 – CareySon – 博客园.

引言

T-SQL不仅仅是一个用于查询数据库的语言,还是一个可以对数据进行操作的语言。基于列的CASE表达式就是其中一种,不像其他查询语句可以互相替代 (比如用子查询实现的查询也可以使用Join实现),CASE表达式在控制基于列的逻辑大部分是无法替代的。下面文中会详细讲解CASE表达式。

简介

基于列的逻辑表达式,其实就是CASE表达式.可以用在SELECT,UPDATE,DELETE,SET以及IN,WHERE,ORDER BY和HAVING子句之后。由于这里讲的是T-SQL查询,所以只说到CASE表达式在SELECT子句和ORDER BY子句中的使用。

CASE表达式实现的功能类似于编程语言中的IF…THEN…ELSE逻辑。只是CASE表达式在T-SQL中并不能控制T-SQL程序的流程,只是作为基于列的逻辑使用.

一个简单的CASE表达式如下:

我已经知道员工ID对应的姓名,我想获得员工ID,并将员工ID以姓名的方式展现出来,我不知道的员工ID则显示UNKNOW:

SELECT TOP 4 CASE EmployeeID
	WHEN 1 THEN 'CareySon'
	WHEN 2 THEN 'Jack'
	WHEN 3 THEN 'Tom'
	ELSE 'UNKNOW'
	END AS NameList,EmployeeID
  FROM [AdventureWorks].[HumanResources].[Employee]
  ORDER BY EmployeeID

显示结果如下:

1

上面代码中,CASE后面跟选择的列名,后面的WHEN所取得值都为EmployeeID这一列,THEN后面的值为对应前面WHEN后面列中,实际在结果中显示的值。

CASE表达式实际情况可以分为两种:

CASE简单表达式(CASE Simple Expression):将某个表达式与一组简单表达式进行比较以确定结果。

CASE 搜索表达式(CASE Searched Expression):计算一组布尔表达式以确定结果。

下面会按照这两种CASE表达式来阐述

CASE简单表达式(CASE Simple Expression)

在CASE简单表达式中,整个表达式只会取一列的值做相应的判断,上面那个查询例子就是一个CASE简单表达式,可以用下图表示:

2

CASE表达式也可以用这样的写法:

SELECT TOP 4 NameList=CASE EmployeeID
	WHEN 1 THEN 'CareySon'
	WHEN 2 THEN 'Jack'
	WHEN 3 THEN 'Tom'
	ELSE 'UNKNOW'
	END,EmployeeID
  FROM [AdventureWorks].[HumanResources].[Employee]
  ORDER BY EmployeeID

上面代码和前面代码所达到的效果是一模一样的,从这个代码可以看出,CASE表达式的结果实际上只局限在一列当中,这也是为什么CASE表达式是“基于列的逻辑表达式”

因为CASE表达式的值只局限在一列当中,所以THEN后面的值数据类型必须相同,或者兼容,否则就会报错。

在上面语句中,还有一个可选的“ELSE”语句,这个语句可以省略,但最好的做法是保留ELSE,否则不在匹配值范围内的所有值都会为“NULL”。

CASE搜索表达式(CASE Searched Expression)

与CASE简单表达式不同,CASE搜索表达式提供了更强大的功能,CASE搜索表达式不仅可以使用更复杂的逻辑表达式,并且还能根据多列中的数据确定所显示列的值。与上面CASE简单表达式等效的CASE搜索表达式为:

SELECT TOP 4 NameList=CASE 
	WHEN EmployeeID=1 THEN 'CareySon'
	WHEN EmployeeID=1 THEN 'Jack'
	WHEN EmployeeID=3 THEN 'Tom'
	ELSE 'UNKNOW'
	END,EmployeeID
  FROM [AdventureWorks].[HumanResources].[Employee]
  ORDER BY EmployeeID

CASE搜索表达式更复杂的应用比如:

公司规定每个人病假或者休假每年都不应该超过30个小时,现在我想取得所有男性员工的ID,其中员工病假或者是休假任意一项超过了30个小时,标记为“Exceed the time”,两项都不超过30个小时的,标记为“Not Exceed the time”

SELECT EmployeeID,
      CASE 
      WHEN VacationHours>30 AND Gender='M' THEN 'Exceed The Time'
      WHEN SickLeaveHours>30 AND Gender='M' THEN 'Exceed The Time'
      ELSE 'Not Exceed The Time'
      END AS Condition
  FROM [AdventureWorks].[HumanResources].[Employee]

查询结果如下:

3

上面可以看到,搜索表达式一列的WHEN表达式可以取自不同列,甚至是不同列之间的运算(比如上面可以取WHEN VacationHours+SickLeaveHours>60),这大大增强了CASE表达式的功能,因为CASE搜索表达式可以完全实现 CASE简单表达式所能实现的功能,我个人认为所有的CASE表达式都应该写成CASE搜索表达式的形式。

还有要注意WHEN…THEN是以先后顺序出现,当第一个WHEN后面的表达式为FALSE时,则会去看第二个WHEN后的表达式,依次类推。当第一个WHEN后的表达式为TRUE时,则取第一个THEN后面的值,即使第二个WHEN表达式也为TRUE。

例如还是第一个例子:我已经知道员工ID对应的姓名,我想获得员工ID,并将员工ID以姓名的方式展现出来,我不知道的员工ID则显示UNKNOW:

SELECT  NameList=CASE
	WHEN EmployeeID=1 THEN 'CareySon'
	WHEN EmployeeID=1 THEN 'Jack'
	WHEN EmployeeID=3 THEN 'Tom'
	ELSE 'UNKNOW'
	END,EmployeeID
  FROM [AdventureWorks].[HumanResources].[Employee]
  ORDER BY EmployeeID

结果如下:

4

CASE表达式在ORDER BY中的使用

CASE表达式在ORDER BY中可以将排序结果分类,使符合某些条件的行(Row)采用一种排序方式,符合另一种条件的行采用另一种排序方式:

比如:我想查看省份ID为8和9的员工的地址,当省份ID为9时,按照AddressID降序排列,当省份ID为8时,按照AddressID升序排列

SELECT [AddressID]
      ,[AddressLine1]
      ,[City]
      ,[StateProvinceID]
      FROM [AdventureWorks].[Person].[Address]
WHERE StateProvinceID=9 OR StateProvinceID=8
ORDER BY  
         CASE WHEN StateProvinceID=9 THEN AddressID END DESC,
         CASE WHEN StateProvinceID=8 THEN AddressID END

结果如下:

5

注意这里,每一条排序规则都要写一个单独的CASE表达式,前面文章说了,因为CASE表达式是基于列的,一个CASE表达式只能返回一个值,所以基于多少个值排序,则需要多少个CASE表达式

总结

文章讲述了CASE表达式在SELECT子句中和ORDER BY子句中的使用,CASE表达式又进一步分为CASE简单表达式和CASE搜索表达式。掌握使用CASE表达式可以使程序员将一部分需要在程序中的业务 逻辑移到数据库中。掌握CASE表达式是深入学习T-SQL查询必不可少的。

赞(0) 打赏
分享到: 更多 (0)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏