在T—SQL中实现字符串类型的聚合统计查询的一种方法

罗瑞明

摘要：在SQL Server数据库中，没有对字符串数据类型进行聚合统计的函数，但是实际应用中确实会有这方面的需求。文章通过对一条常用T-SQL查询语句的分析，进行优化，并编写自定义函数，实现了对字符串数据类型进行聚合统计的功能，提供具有实际应用价值的参考。

关键词：SQL Server；字符串；聚合统计；查询

中图分类号：TP393 文献标识码：A 文章编号：1009-3044（2017）02-0008-02

我們知道，在SQL中进行统计查询可以通过聚合函数来实现，常用的聚合函数有：

l AVG（expression）函数----按列计算平均值

l SUM（expression）函数----按列计算值的总和

l MAX（expression）函数----求一列中的最大值

l MIN（expression）函数----求一列中的最小值

l COUNT（expression）函数----按列值统计个数

但是，我们来看这样一个问题，在如下图1所示的一个产品表中，需要按照产品类别分类，将所有同类别的商品名称合并在一起，中间用逗号“，”间隔开显示。期望查询的结果如图2所示：

由此我们想到，需要用SQL查询的聚合函数来进行分组统计，分组的字段应该是产品类别，即Group By CategroyId， 而需要进行聚合统计的字段是ProductName。但是ProductName字段是字符串数据类型，在SQL Server 2012之前的版本中都没有对字符串进行连接合并的聚合函数。那么，如何实现对字符串的连接合并呢？ 一开始，我们可能会想到使用游标来操作，因为只有使用游标才能方便定位在每一条记录上，将ProductName字段值提取出来，再依次进行字符串连接操作，以得到期望的结果。这个过程比较麻烦，能不能有更简便有效的方法呢？

我们知道，使用T-SQL从表中读取字段值到变量时，可以这样：

Select @变量 = 字段 From 表 Where 条件

代码如下：

Declare @name varchar（20）

Select @name = name From tb_student Where stuNo = '200200001'

Print @name

结果显示：黄清

通常，我们必须保证这条给变量赋值的Select查询语句，只能查找到一条记录，这样就刚好将该记录的name字段值赋值给@name变量；但是，如果Select查询的结果是多条记录呢？这样意味着要将多个字段的值赋给1个变量，会不会导致程序出错呢？如以下代码：

Declare @name varchar（20）

Select @name = name From tb_student Where stuNo Like '2002%'

Print @name

实践证明，程序不会出错，但是@name变量的值也不可能是多个值，仅仅是得到结果集中最后一条记录的name字段值。

结果显示：徐世亮

原来经过是这样的，其实在select查询语句查询的时候，确实查询出了多条结果记录，而结果集中每查出一条记录，都会向@name变量赋值一次，后面的值覆盖了前面的值，导致@name变量的最终的结果为最后一条记录的name字段值。 不信的话，我们可以使用另一段程序来验证该过程。

Declare @name varchar（200）

set @name = ''

Select @name = @name + name From tb_student Where stuNo Like '2002%'

Print @name

这一次，我们用累加的方式，将select每次查询出的字段值依次连接在@name变量的后面。

结果显示：黄清张楠田淑芳李强王兰徐世亮

从这个结果可以看出， Select查询语句每查出一条记录都将字段值赋值给了@name变量，通过@name = @name + name不断累加，所有字段值都连接在了一起。

由此，我们想到，如果我们需要将结果连接在一起，是不是可以采用此“累加”的方法，这样可以省去使用游标来操作的麻烦。

下面，我们就利用此功能，将字符串类型的字段进行分组统计合并。

首先，我们设计一个自定义聚合函数，用于将相同CategoryId的ProductName字符串进行连接合并。

CREATE FUNCTION mergeName（@cateGoryid int）

RETURNS NVARCHAR（255）

AS

BEGIN

DECLARE @r NVARCHAR（255）

SET @r = ''

SELECT @r = @r + '，' + productName FROM Product

WHERE CategoryId = @CategoryId

RETURN STUFF（@r， 1， 1， ''）

END

其中：

SELECT @r = @r + '，' + productName FROM Product WHERE CategoryId = @CategoryId的作用就是将某CategoryId的所有productName连接合并，然后使用系统自带函数STUFF（）将合并后的字符串的首字符“，”逗号去除掉（因为一开始@r=，连接后，势必会在首字符位置多出一个逗号）。

然后，我们在进行聚合统计查询时，就可以使用该函数进行字符串连接操作了：

SELECT CategoryId ，dbo.mergeName（CategoryId） AS productNames

FROM product GROUP BY CategoryId

结果如图4所示：

结论：

大功告成，总结一下实现的原理和过程：

首先，利用Select @变量 = @变量 + 字段 From 表 Where 条件 的查询，可以将查询到的结果集中的字符串字段值连接起来，将该过程定义成自定义聚合函数。

然后，在分组统计查询时，使用该聚合函数将每一组的字符串字段值进行连接操作。

如果要使字符串连接的结果更易于阅读，可以在连接字符串时加上分隔符，并将最后结果进行去除多余字符的处理，如使用STUFF（）等函数。

如果希望连接字符串中的分隔符不一定是“，”逗号，也可以是自定义的符号，则只需要稍微修改前面的自定义聚合函数（mergeName）的代码即可。

后语：

除了上面介绍的方法外，T-SQL还支持FOR XML PATH将查询结果集以XML形式展现，它也可以简化查询语句以实现一些可能需要借助函数或存储过程来完成的工作。

另外，在SQL Server 2012之后的版本，也增加了一个类似于MySQL数据库中的Group_Concat（）函数，它能将相同的行组合起来，比起自己定义函数，要方便很多。有兴趣的读者也可以了解以上的一些技术。

参考文献：

[1] 何继业，易丹，陈国荣.数据库应用技术-SQL Server 2008[M].中国水利水电出版社，2014.

[2] 微软公司. SQL Server 2005数据库开发与实现[M].高等教育出版社，2008.