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

本文我们将讨论如何使用groupby子句来汇总数据。

使用单独列分组

group by子句通过设置分组条件来汇总数据，在第一个例子中，我在数据库adventurework2012中的表 sales.salesorderdetail.中的一列上进行数据分组操作。这个例子以及其他例子都使用数据库adventureworks2012，如果你想使用它运行我的代码，你可以点击下载。

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

use adventureworks2012; 
 
go 
 
select carriertrackingnumber 
 
,sum(linetotal) as summarizedlinetotal 
 
from adventureworks2012.sales.salesorderdetail 
 
group by carriertrackingnumber; 

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

carriertrackingnumber linetotal 
 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
 
6e46-440a-b5 2133.170700 
 
b501-448e-96 4085.409800 
 
8551-4cdf-a1 72616.524200 
 
b65c-4867-86 714.704300 
 
99ce-4ada-b1 16185.429200 

在上面的示例中，我使用group by子句选择哪些列作为聚集dventureworks2012.sales.salesorderdetai数据表数据的条件，在例子中，我使用carriertrackingnumber汇总数据，当你进行数据分组时，只有在group by 子句中出现的列才在selection 列表中有效。在我的示例中，我使用聚集函数sum计算linetotal，为了使用方便，我为它设置了别名summarizedlinetotal。

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

use adventureworks2012; 
 
go 
 
select carriertrackingnumber 
 
,sum(linetotal) as summarizedlinetotal 
 
from adventureworks2012.sales.salesorderdetail 
 
where carriertrackingnumber = '48f2-4141-9a' 
 
group by carriertrackingnumber; 

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

通过多列来分组

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

select d.productid 
 
, h.orderdate 
 
, sum(linetotal) as summarizedlinetotal 
 
from adventureworks2012.sales.salesorderdetail d 
 
join adventureworks2012.sales.salesorderheader h 
 
on d.salesorderid = h.salesorderid 
 
group by productid, orderdate; 

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

productid orderdate linetotal 
 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
 
714 2008-05-21 00:00:00.000 99.980000 
 
859 2007-11-03 00:00:00.000 48.980000 
 
923 2007-11-23 00:00:00.000 14.970000 
 
712 2007-12-22 00:00:00.000 62.930000 
 
795 2007-10-14 00:00:00.000 2443.350000 
 
950 2007-07-01 00:00:00.000 2462.304000 
 
795 2007-11-06 00:00:00.000 2443.350000 
 
877 2007-11-19 00:00:00.000 15.900000 
 
713 2007-10-01 00:00:00.000 99.980000 
 
860 2008-05-31 00:00:00.000 48.980000 
 
961 2008-05-01 00:00:00.000 36242.120880 

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

select d.productid 
 
, h.orderdate 
 
, sum(linetotal) as summarizedlinetotal 
 
from adventureworks2012.sales.salesorderdetail d 
 
join adventureworks2012.sales.salesorderheader h 
 
on d.salesorderid = h.salesorderid 
 
group by productid, orderdate 
 
order by summarizedlinetotal desc; 

在上面的代码中，我按照summorizedlinetotal降序对结果集进行排序，此列的值通过group by子句分组后对linetotal使用聚合函数sum得到。我对结果按照summorizedlinetotal 的值降序排列。如果你运行此程序，你可以得出linetotal 数量最高的productid和orderdate。

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

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

create table nullgroupby (orderdate date, amount int); 
 
insert into nullgroupby values (null,100), 
 
('10-30-2014',100), 
 
('10-31-2014',100), 
 
(null,100); 
 
select orderdate, sum(amount) as totalamount 
 
from nullgroupby 
 
group by orderdate; 
 
drop table nullgroupby; 
 
when i run this code i get the following output: 
 
orderdate totalamount 
 
-- -- -- -- -- -- -- -- -- -- - 
 
null 200 
 
2014-10-30 100 
 
2014-10-31 100 

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

在group by 子句中使用表达式

有时你需要在group by子句中使用表达式，而不是具体的列。sql server允许你在group by子句中指定一个表达式，就像下面的代码中所示：

select convert(char(7),h.orderdate,120) as [yyyy-mm] 
 
, sum(linetotal) as summarizedlinetotal 
 
from adventureworks2012.sales.salesorderdetail d 
 
join adventureworks2012.sales.salesorderheader h 
 
on d.salesorderid = h.salesorderid 
 
group by convert(char(7),h.orderdate,120) 
 
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子句，请参考下面代码：

select d.productid 
 
, h.orderdate 
 
, sum(linetotal) as summarizedlinetotal 
 
from adventureworks2012.sales.salesorderdetail d 
 
join adventureworks2012.sales.salesorderheader h 
 
on d.salesorderid = h.salesorderid 
 
group by productid, orderdate 
 
having sum(linetotal) > 200000 
 
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子句对汇总数据进行特定的过滤。希望下次你需要汇总一些数据的时候，你可以更加容易的达到目的。