SQL Server数据汇总五招轻松搞定
本文我们将讨论如何使用GROUPBY子句来汇总数据。
使用单独列分组
GROUPBY子句通过设置分组条件来汇总数据,在第一个例子中,我在数据库AdventureWork2012中的表Sales.SalesOrderDetail.中的一列上进行数据分组操作。这个例子以及其他例子都使用数据库AdventureWorks2012,如果你想使用它运行我的代码,你可以点击下载。
下面是第一个示例的源码,在CarrierTrackingNumber列上使用groupby子句进行数据分组操作
USEAdventureWorks2012; GO SELECTCarrierTrackingNumber ,SUM(LineTotal)ASSummarizedLineTotal FROMAdventureWorks2012.Sales.SalesOrderDetail GROUPBYCarrierTrackingNumber;
在我运行这段代码后,会得到3807个记录,下面是这个庞大的结果集中前五个数值:
CarrierTrackingNumberLineTotal ---------------------------------------------------------- 6E46-440A-B52133.170700 B501-448E-964085.409800 8551-4CDF-A172616.524200 B65C-4867-86714.704300 99CE-4ADA-B116185.429200
在上面的示例中,我使用groupby子句选择哪些列作为聚集dventureWorks2012.Sales.SalesOrderDetai数据表数据的条件,在例子中,我使用CarrierTrackingNumber汇总数据,当你进行数据分组时,只有在groupBy子句中出现的列才在selection列表中有效。在我的示例中,我使用聚集函数SUM计算LineTotal,为了使用方便,我为它设置了别名SummarizedLineTotal。
如果我想获得CarrierTrackingNumber满足特定条件下的聚集集合,那我可以在Where子句中对查询进行限制,就像我下面做的这样:
USEAdventureWorks2012; GO SELECTCarrierTrackingNumber ,SUM(LineTotal)ASSummarizedLineTotal FROMAdventureWorks2012.Sales.SalesOrderDetail WHERECarrierTrackingNumber='48F2-4141-9A' GROUPBYCarrierTrackingNumber;
这里我在原始查询基础上在where子句中加上了一条限制,我设置了我的查询只返回CarrierTrackingNumber等于一个特定值的结果。运行这段代码后,我会得到记录中CarrierTrackingNumber等于48F2-4141-9A的行的数量。Where子句的过滤行为在数据被聚集之前就已生效。
通过多列来分组
有时候你可能需要使用多列来进行数据分组,下面是我使用多列进行分组的示例代码
SELECTD.ProductID ,H.OrderDate ,SUM(LineTotal)ASSummarizedLineTotal FROMAdventureWorks2012.Sales.SalesOrderDetailD JOINAdventureWorks2012.Sales.SalesOrderHeaderH OND.SalesOrderId=H.SalesOrderID GROUPBYProductID,OrderDate;
查询返回26878行数据,这是上面查询返回的部分结果:
ProductIDOrderDateLineTotal ---------------------------------------------------------- 7142008-05-2100:00:00.00099.980000 8592007-11-0300:00:00.00048.980000 9232007-11-2300:00:00.00014.970000 7122007-12-2200:00:00.00062.930000 7952007-10-1400:00:00.0002443.350000 9502007-07-0100:00:00.0002462.304000 7952007-11-0600:00:00.0002443.350000 8772007-11-1900:00:00.00015.900000 7132007-10-0100:00:00.00099.980000 8602008-05-3100:00:00.00048.980000 9612008-05-0100:00:00.00036242.120880
在上面的示例程序中,Groupby子句中用到了ProductID列与OrderDate列,SQLServer基于ProductID和OrderDate二者组合的唯一性,返回LineTotal的值,并为其设置别名SummarizedLineTotal。如果你查看程序的输出,你会发现SQLServer对数据进行分组后,返回的结果并没有特定的顺序,如果你需要返回结果按照一定顺序排序,你需要使用ORDERBY子句,就像我在下面代码中展示的那样。
SELECTD.ProductID ,H.OrderDate ,SUM(LineTotal)ASSummarizedLineTotal FROMAdventureWorks2012.Sales.SalesOrderDetailD JOINAdventureWorks2012.Sales.SalesOrderHeaderH OND.SalesOrderId=H.SalesOrderID GROUPBYProductID,OrderDate ORDERBYSummarizedLineTotalDESC;
在上面的代码中,我按照SummorizedLineTotal降序对结果集进行排序,此列的值通过GroupBY子句分组后对LineTotal使用聚合函数Sum得到。我对结果按照SummorizedLineTotal的值降序排列。如果你运行此程序,你可以得出LineTotal数量最高的ProductID和OrderDate。
对没有任何值的数据进行分组
有时候你会需要对一些记录中包含空值的数据进行分组操作。当你在SQLServer执行此类操作时,它会自动假设所有NULL值相等。让我看一下下面的示例程序
CREATETABLENullGroupBy(OrderDatedate,AmountInt); INSERTINTONullGroupByvalues(NULL,100), ('10-30-2014',100), ('10-31-2014',100), (NULL,100); SELECTOrderDate,SUM(Amount)asTotalAmount FROMNullGroupBy GROUPBYOrderDate; DROPTABLENullGroupBy; WhenIrunthiscodeIgetthefollowingoutput: OrderDateTotalAmount --------------------- NULL200 2014-10-30100 2014-10-31100
在上面的程序中,我首先创建并填充了一个NullGroupBy表.在这个表中,我放置了四个不同的行,第一行和最后一行的orderDate列值为NULL,其他两列的orderDate值不同。从上面的输出结果可以看到,SQLServer在分组时将OrderDate为NULL的两行聚集为一行处理。
在GroupBY子句中使用表达式
有时你需要在Groupby子句中使用表达式,而不是具体的列。SQLServer允许你在GroupBy子句中指定一个表达式,就像下面的代码中所示:
SELECTCONVERT(CHAR(7),H.OrderDate,120)AS[YYYY-MM] ,SUM(LineTotal)ASSummarizedLineTotal FROMAdventureWorks2012.Sales.SalesOrderDetailD JOINAdventureWorks2012.Sales.SalesOrderHeaderH OND.SalesOrderId=H.SalesOrderID GROUPBYCONVERT(CHAR(7),H.OrderDate,120) ORDERBYSummarizedLineTotalDESC;
上述代码使用OrderDate列中的年月数据进行分组,通过使用表达式CONVERT(CHAR(7),H.OrderDate,120),我告诉SQL服务器截取OrderDateODBC标准日期格式的前七个字符,也就是OrderDateyyyy-mm部分。基于这个表达式,我可以找出特定年月的totalSummarizeLineTotal值,在GroupBy子句中使用表达式,以及对LineTotal值排序,我可以找出哪一年哪一个月的SummarizeLineTotal最大或最小。
使用HAVING子句过滤数据
Having是另外一个能与GroupBY子句结合使用的重要子句,使用Having子句,你可以过滤掉不符合Having子句所接表达式的数据行,当我在其上使用where子句时,在聚集之前就会产生过滤行为。Having子句允许你基于某些标准过滤聚合行。想要更清楚地了解Having子句,请参考下面代码:
SELECTD.ProductID ,H.OrderDate ,SUM(LineTotal)ASSummarizedLineTotal FROMAdventureWorks2012.Sales.SalesOrderDetailD JOINAdventureWorks2012.Sales.SalesOrderHeaderH OND.SalesOrderId=H.SalesOrderID GROUPBYProductID,OrderDate HAVINGSUM(LineTotal)>200000 ORDERBYSummarizedLineTotalDESC;
在上面的代码中Having子句限制条件是SUM(LineTotal)>200000。这个having子句保证最终结果中LineTotal的聚合值(SummarizedLineTotal)大于200000.通过使用having子句,我的查询只返回一行数据,其SummarizedLineTotal大于200000.having子句允许SQLServer只返回聚合结果满足Having子句限制的条件的数据行。
总结
许多应用要求数据在展示之前要经过一定的汇总操作,GROUPBY子句就是SQLServer提供的汇总数据的机制。GROUPBY子句允许你使用HAVING子句对汇总数据进行特定的过滤。希望下次你需要汇总一些数据的时候,你可以更加容易的达到目的。