五招轻松搞定SQL Server数据汇总

数据库 SQL Server 数据库运维
有些时候你想让SQL Server 返回一个聚集结果集合,而不是一个详细的结果集。SQL Server的GROUPBY子句,为你提供了一种聚合SQL Server数据的方式。GROUPBY子句允许你在一列或多列数据甚至是表达式上进行分组操作,在这篇文章中,我将讨论如何使用GROUPBY子句来汇总数据。

有些时候你想让SQL Server 返回一个聚集结果集合,而不是一个详细的结果集。SQL Server的GROUPBY子句,为你提供了一种聚合SQL Server数据的方式。GROUPBY子句允许你在一列或多列数据甚至是表达式上进行分组操作,在这篇文章中,我将讨论如何使用GROUPBY子句来汇总数据。

使用单独列分组

GROUP BY子句通过设置分组条件来汇总数据,在第一个例子中,我在数据库AdventureWork2012中的表 Sales.SalesOrderDetail.中的一列上进行数据分组操作。这个例子以及其他例子都使用数据库AdventureWorks2012,如果你想使用它运行我的代码,你可以点击下载。

下面是第一个示例的源码,在CarrierTrackingNumber列上使用group by子句进行数据分组操作

  1. USE AdventureWorks2012; 
  2.  
  3. GO 
  4.  
  5. SELECT CarrierTrackingNumber 
  6.  
  7. ,SUM(LineTotal) AS SummarizedLineTotal 
  8.  
  9. FROM AdventureWorks2012.Sales.SalesOrderDetail 
  10.  
  11. GROUP BY CarrierTrackingNumber; 

在我运行这段代码后,会得到3807个记录,下面是这个庞大的结果集中前五个数值:

  1. CarrierTrackingNumber LineTotal 
  2.  
  3. -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
  4.  
  5. 6E46-440A-B5 2133.170700 
  6.  
  7. B501-448E-96 4085.409800 
  8.  
  9. 8551-4CDF-A1 72616.524200 
  10.  
  11. B65C-4867-86 714.704300 
  12.  
  13. 99CE-4ADA-B1 16185.429200 

在上面的示例中,我使用group by子句选择哪些列作为聚集dventureWorks2012.Sales.SalesOrderDetai数据表数据的条件,在例子中,我使用CarrierTrackingNumber汇总数据,当你进行数据分组时,只有在group By 子句中出现的列才在selection 列表中有效。在我的示例中,我使用聚集函数SUM计算LineTotal,为了使用方便,我为它设置了别名SummarizedLineTotal。

如果我想获得CarrierTrackingNumber 满足特定条件下的聚集集合,那我可以在Where子句中对查询进行限制,就像我下面做的这样:

  1. USE AdventureWorks2012; 
  2.  
  3. GO 
  4.  
  5. SELECT CarrierTrackingNumber 
  6.  
  7. ,SUM(LineTotal) AS SummarizedLineTotal 
  8.  
  9. FROM AdventureWorks2012.Sales.SalesOrderDetail 
  10.  
  11. WHERE CarrierTrackingNumber = '48F2-4141-9A' 
  12.  
  13. GROUP BY CarrierTrackingNumber; 

这里我在原始查询基础上在where子句中加上了一条限制,我设置了我的查询只返回CarrierTrackingNumber 等于一个特定值的结果。运行这段代码后,我会得到记录中CarrierTrackingNumber 等于48F2-4141-9A的行的数量。Where子句的过滤行为在数据被聚集之前就已生效。

通过多列来分组

有时候你可能需要使用多列来进行数据分组,下面是我使用多列进行分组的示例代码

  1. SELECT D.ProductID 
  2.  
  3. , H.OrderDate 
  4.  
  5. SUM(LineTotal) AS SummarizedLineTotal 
  6.  
  7. FROM AdventureWorks2012.Sales.SalesOrderDetail D 
  8.  
  9. JOIN AdventureWorks2012.Sales.SalesOrderHeader H 
  10.  
  11. ON D.SalesOrderId = H.SalesOrderID 
  12.  
  13. GROUP BY ProductID, OrderDate; 

查询返回26878行数据,这是上面查询返回的部分结果:

  1. ProductID OrderDate LineTotal 
  2.  
  3. -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
  4.  
  5. 714 2008-05-21 00:00:00.000 99.980000 
  6.  
  7. 859 2007-11-03 00:00:00.000 48.980000 
  8.  
  9. 923 2007-11-23 00:00:00.000 14.970000 
  10.  
  11. 712 2007-12-22 00:00:00.000 62.930000 
  12.  
  13. 795 2007-10-14 00:00:00.000 2443.350000 
  14.  
  15. 950 2007-07-01 00:00:00.000 2462.304000 
  16.  
  17. 795 2007-11-06 00:00:00.000 2443.350000 
  18.  
  19. 877 2007-11-19 00:00:00.000 15.900000 
  20.  
  21. 713 2007-10-01 00:00:00.000 99.980000 
  22.  
  23. 860 2008-05-31 00:00:00.000 48.980000 
  24.  
  25. 961 2008-05-01 00:00:00.000 36242.120880 

在上面的示例程序中,Group by 子句中用到了ProductID列与OrderDate列,SQL Server基于ProductID和OrderDate二者组合的唯一性,返回LineTotal的值,并为其设置别名SummarizedLineTotal。如果你查看程序的输出,你会发现SQL Server 对数据进行分组后,返回的结果并没有特定的顺序,如果你需要返回结果按照一定顺序排序,你需要使用ORDER BY 子句,就像我在下面代码中展示的那样。

  1. SELECT D.ProductID 
  2.  
  3. , H.OrderDate 
  4.  
  5. SUM(LineTotal) AS SummarizedLineTotal 
  6.  
  7. FROM AdventureWorks2012.Sales.SalesOrderDetail D 
  8.  
  9. JOIN AdventureWorks2012.Sales.SalesOrderHeader H 
  10.  
  11. ON D.SalesOrderId = H.SalesOrderID 
  12.  
  13. GROUP BY ProductID, OrderDate 
  14.  
  15. ORDER BY SummarizedLineTotal DESC

在上面的代码中,我按照SummorizedLineTotal降序对结果集进行排序,此列的值通过Group BY子句分组后对LineTotal使用聚合函数Sum得到。我对结果按照SummorizedLineTotal 的值降序排列。如果你运行此程序,你可以得出LineTotal 数量最高的ProductID和OrderDate。

对没有任何值的数据进行分组

有时候你会需要对一些记录中包含空值的数据进行分组操作。当你在SQL Server 执行此类操作时,它会自动假设所有NULL值相等。让我看一下下面的示例程序

  1. CREATE TABLE NullGroupBy (OrderDate date, Amount Int); 
  2.  
  3. INSERT INTO NullGroupBy values (NULL,100), 
  4.  
  5. ('10-30-2014',100), 
  6.  
  7. ('10-31-2014',100), 
  8.  
  9. (NULL,100); 
  10.  
  11. SELECT OrderDate, SUM(Amount) as TotalAmount 
  12.  
  13. FROM NullGroupBy 
  14.  
  15. GROUP BY OrderDate; 
  16.  
  17. DROP TABLE NullGroupBy; 
  18.  
  19. When I run this code I get the following output
  20.  
  21. OrderDate TotalAmount 
  22.  
  23. -- -- -- -- -- -- -- -- -- -- - 
  24.  
  25. NULL 200 
  26.  
  27. 2014-10-30 100 
  28.  
  29. 2014-10-31 100 

在上面的程序中,我首先创建并填充了一个NullGroupBy表.在这个表中,我放置了四个不同的行,第一行和最后一行的orderDate列值为NULL,其他两列的orderDate值不同。从上面的输出结果可以看到,SQL Server 在分组时将OrderDate为NULL的两行聚集为一行处理。

在Group BY 子句中使用表达式

有时你需要在Group by子句中使用表达式,而不是具体的列。SQL Server允许你在Group By子句中指定一个表达式,就像下面的代码中所示:

  1. SELECT CONVERT(CHAR(7),H.OrderDate,120) AS [YYYY-MM] 
  2.  
  3. SUM(LineTotal) AS SummarizedLineTotal 
  4.  
  5. FROM AdventureWorks2012.Sales.SalesOrderDetail D 
  6.  
  7. JOIN AdventureWorks2012.Sales.SalesOrderHeader H 
  8.  
  9. ON D.SalesOrderId = H.SalesOrderID 
  10.  
  11. GROUP BY CONVERT(CHAR(7),H.OrderDate,120) 
  12.  
  13. ORDER BY SummarizedLineTotal DESC

上述代码使用OrderDate 列中的年月数据进行分组,通过使用表达式CONVERT(CHAR(7),H.OrderDate,120) ,我告诉SQL服务器截取OrderDate ODBC标准日期格式的前七个字符,也就是OrderDate yyyy-mm部分。基于这个表达式,我可以找出特定年月的total SummarizeLineTotal值,在Group By子句中使用表达式,以及对LineTotal值排序,我可以找出哪一年哪一个月的SummarizeLineTotal最大或最小。

使用HAVING子句过滤数据

Having 是另外一个能与Group BY 子句结合使用的重要子句,使用Having 子句,你可以过滤掉不符合Having子句所接表达式的数据行,当我在其上使用where子句时,在聚集之前就会产生过滤行为。Having 子句允许你基于某些标准过滤聚合行。想要更清楚地了解Having子句,请参考下面代码:

  1. SELECT D.ProductID 
  2.  
  3. , H.OrderDate 
  4.  
  5. SUM(LineTotal) AS SummarizedLineTotal 
  6.  
  7. FROM AdventureWorks2012.Sales.SalesOrderDetail D 
  8.  
  9. JOIN AdventureWorks2012.Sales.SalesOrderHeader H 
  10.  
  11. ON D.SalesOrderId = H.SalesOrderID 
  12.  
  13. GROUP BY ProductID, OrderDate 
  14.  
  15. HAVING SUM(LineTotal) > 200000 
  16.  
  17. ORDER BY SummarizedLineTotal DESC

在上面的代码中Having子句限制条件是SUM(LineTotal) > 200000。这个having子句保证最终结果中LineTotal的聚合值(SummarizedLineTotal)大于200000.通过使用having子句,我的查询只返回一行数据,其SummarizedLineTotal大于200000.having子句允许SQL Server 只返回聚合结果满足Having子句限制的条件的数据行。

总结

许多应用要求数据在展示之前要经过一定的汇总操作,GROUP BY子句就是SQL Server提供的汇总数据的机制。GROUPBY子句允许你使用HAVING子句对汇总数据进行特定的过滤。希望下次你需要汇总一些数据的时候,你可以更加容易的达到目的。

责任编辑:Ophira 来源: TechTarget中国
相关推荐

2010-08-25 15:24:13

职业定位

2015-09-24 10:06:59

2012-03-31 09:31:54

2022-11-16 09:57:23

优化接口

2011-09-29 15:30:57

云计算

2020-02-04 12:44:03

混合云架构公共云

2019-12-20 08:00:00

云账号劫持网络钓鱼云安全

2009-01-03 09:14:00

网络模块选购

2015-09-23 09:43:59

2010-01-20 11:09:18

虚拟服务器安全

2020-06-03 07:00:12

云成本监控工具云散乱

2010-10-22 11:31:53

SQL Server自

2011-06-09 09:59:16

2018-03-22 12:44:13

2009-10-09 09:39:47

2010-08-25 13:31:22

网络故障排除

2011-08-02 09:01:14

2011-08-03 09:22:25

MySQL可扩展性

2009-02-23 14:21:16

2015-05-29 17:00:48

XY
点赞
收藏

51CTO技术栈公众号