SQL查询入门（下篇）

引言

      在前两篇文章中，对于单表查询和多表查询的概念做出了详细的介绍，在本篇文章中会主要介绍聚合函数的使用和数据的分组.

 

简介

      简单的说，聚合函数是按照一定的规则将多行(Row)数据汇总成一行的函数。对数据进行汇总前，还可以按照特定的列(column)将数据进行分组(Group by)再汇总,然后按照再次给定的条件进行筛选(Having).

 

      聚合函数将多行数据进行汇总的概念可以简单用下图解释:

      

 

 

简单聚合函数

       简单聚合函数是那些拥有很直观将多行(Row)汇总为一行(Row)计算规则的函数。这些函数往往从函数名本身就可以猜测出函数的作用，而这些函数的参数都是数字类型的。简单聚合函数包括:Avg,Sum,Max,Min.

       简单聚合函数的参数只能是数字类型，在SQL中，数字类型具体包括:tinyint,smallint,int,bigint,decimal,money,smallmoney,float,real.

       在介绍简单聚合函数之前，先来介绍一下Count()这个聚合函数.

 

      Count()

       Count函数用于计算给定条件下所含有的行(Row)数.例如最简单的:

       

       上表中，我想知道公司员工的个数，可以简单的使用:

SELECT     COUNT(*) AS EmployeeNumber
FROM         HumanResources.Employee

    

        结果如下

       

         当Count()作用于某一特定列(Column)，和以“*”作为参数时的区别是当Count(列名)碰到“Null”值时不会将其计算在内,例如:

        我想知道公司中有上级的员工个数:

SELECT     COUNT(ManagerID) AS EmployeeWithSuperior
FROM       HumanResources.Employee

      

 

      

       可以看到，除了没有上级的CEO之外，所有其他的员工已经被统计在内.

         

       也可以在Count()内使用Distinct关键字来让,每一列（Column）的每个相同的值只有一个被统计在内，比如:

       我想统计公司中经理层级的数量：

SELECT     COUNT(DISTINCT ManagerID) AS NumberOfManager
FROM       HumanResources.Employee

         

       结果如上.

 

        Avg(),Sum(),Max()和Min()

        这几个聚合函数除了功能不同以外，参数和用法几乎相同。所以这里只对Avg()这个聚合函数进行解释:

        Avg()表示计算在选择范围内的汇总数据的平均值.这个过程中“Null”值不会被统计在内 ,例如:

        我想获得平均每位员工休假的时长:

SELECT     AVG(VacationHours) AS 'Average vacation hours'
FROM       HumanResources.Employee

        结果如下:

     

      因为默认用聚合函数进行数据汇总时，不包含null,但如果我想要包含null值，并在当前查询中将Null值以其他值替代并参与汇总运算时，使用IsNull(column,value)

      例如:

      我想获得平均每位员工的休假时长，如果员工没有休假，则按休假10个小时计算

SELECT     AVG(ISNULL(VacationHours, 10)) AS  'Average vacation hours'
FROM       HumanResources.Employee

      结果如下:

     

     也可以使用DISTINCT关键字在简单聚合函数中让每一个值唯一参与聚合汇总运算.在上面的Count函数中已经解释，这里不做重复。

     而关于Sum(),Max(),Min()等这些简单聚合函数，使用方法基本相同，这里就不重复了

 

    

将聚合函数得到的值按照列(Column)进行分组

      如果聚合函数所得到的结果无法按照特定的值进行分组，那聚合函数的作用就没那么强了。在SQL中，使用Group by对聚合函数汇总的值进行分组。分组的概念可以下面这个简单的例子表示：

       例如：

       我想根据不同省得到销售人员所销售的总和:

SELECT     TerritoryID, SUM(SalesLastYear) AS ToTalSales
FROM       Sales.SalesPerson
GROUP BY   TerritoryID

      概念如下图所示:

      

     

   跟在Group by后面的列名是分组的依据。当然在某些情况下，会有依据多个列(Column)进行分组的情况.下面这个例子有点实际意义:

   我想按照不同性别获得不同经理手下的员工的病假时间总和:

 

SELECT     ManagerID, Gender, SUM(SickLeaveHours) AS SickLeaveHours, COUNT(*) AS EmployeeNumber
FROM       HumanResources.Employee
GROUP BY   Gender, ManagerID

  

   结果如下:

    

     Group By后面多列，我们可以在逻辑思维上这么想，先根据每一列唯一的ManagerId和唯一的Gender进行Cross Join(如果你不懂什么Cross join,请看我前面的文章)得到唯一可以确定其他键(Key)的键,最后过滤掉聚合函数中不能返回值的行(Row）（也就是为Null)的行。再根据这实际上两列，但逻辑上是一列的值作为分组依据。

     上图中可以看到，我们首先按照经理ID,进行分组，然后根据不同经理手下的员工的性别，再次进行分总，最终按照这个分组条件得到病假时间总和.

     这里要注意，当使用Group By按照多列(Column)进行分组时，一定要注意出现在Group By后面的次序

     上面先出现Gender是先遍历Gender的所有可能的值，再根据每个Gender可能的值去计算匹配ManagerID，最后再根据ManagerID来进行聚合函数运算，如果将上面Group By后面得列(Column)顺序改为先ManagerId,再Gender,则意味着先遍历ManagerID所有可能出现的值，再去匹配Gender，则结果如下:

    

     从Gender（性别）变为M（男性）开始，第二次遍历ManagerId进行匹配:

     

 

     从上面我们可以看出，虽然Group by后面出现列（Column）的次序不同，所得到结果的顺序也不同，但所得到的数据集(DataSet)是完全一样，所以，可以通过Order By子句将按照不同列次序进行Group By的查询语句获得相同的结果。这里就不再截图了。

 

对分组完成后的数据集进行再次筛选（Having） 

      当对使用聚合函数进行分组后，可以再次使用放在Group By子句后的Having子句对分组后的数据进行再次的过滤.Having子句在某些方面很像Where子句，具体having表达式的使用可以看我前面文章中对where的讲解。Having子句可以理解成在分组后进行二次过滤的语句.

      使用having子句非常简单，但需要注意的是，having子句后面不能跟在select语句中出现的别名，而必须将Select语句内的表达式再写一遍，例如还是针对上面的表：

      我想按照不同性别获得不同经理手下的员工的病假时间总和，这些经理手下的员工需要大于2个人:

SELECT     ManagerID, Gender, SUM(SickLeaveHours) AS SickLeaveHours, COUNT(*) AS EmployeeNumber
FROM       HumanResources.Employee
GROUP BY   ManagerID, Gender
HAVING     (EmployeeNumber > 2)

      注意，上面这句话是错误的,在Having子句后面不能引用别名或者变量名，如果需要实现上面那个效果，需要将Count(*)这个表达式再Having子句中重写一遍,正确写法如下：

SELECT     ManagerID, Gender, SUM(SickLeaveHours) AS SickLeaveHours, COUNT(*) AS EmployeeNumber
FROM       HumanResources.Employee
GROUP BY   ManagerID, Gender
HAVING     (COUNT(*) > 2)

       结果如下：

     

      我们看到，只有员工数大于2人的条件被选中。

 

      当然，Having子句最强大的地方莫过于其可以使用聚合函数作为表达式，这是在Where子句中不允许的。下面这个例子很好的演示了Having子句的强大之处：

      还是上面那个例子的数据：

      我想获得不同经理手下的员工的病假时间总和，并且这个经理手下病假最多的员工的请假小时数大于病假最少员工的两倍：

SELECT     ManagerID, SUM(SickLeaveHours) AS TotalSickLeaveHours, COUNT(*) AS EmployeeNumber
FROM       HumanResources.Employee
GROUP BY   ManagerID
HAVING      (MAX(SickLeaveHours) > 2 * MIN(SickLeaveHours))

   

     结果如下：

    

 

     这里可以看出，Having子句实现如此简单就能实现的强大功能，如果用where将会非常非常的麻烦。上面那个结果中，having语句聚合函数的作用范围可以用下图很好的演示出来:

    

     上面可以看出被筛选后的数据满足请假最多员工的小时数明显大于请假最少员工小时数的两倍。

 

小结

      本文以聚合函数概念为开始，讲述了聚合函数使用中经常用到的查询，分组，过滤的概念和使用方式。使用好聚合函数可以将很多放到应用程序业务层的任务转到数据库里来.这会对维护和性能提升很很大的帮助.

 

PS：SQL查询入门就写完了，虽然文章讲的内容很浅，但我在写作的过程中脑中很多模糊的概念变得逐渐清晰。从而对自己也是一次再学习。我也努力将每一个概念以简单的方式表现出来。技术文章就应该这样吧:-) 后续文章准备中ing….