T-SQL|如何实现聚类连接字符串

长期以来，使用SQL的时候都会遇到如下的需求。例如，我们有如下一张表(名为Cities)：

我们希望得到的结果是：

应该有SQL使用经验的人都会发现，这个过程类似与SQL中的聚类函数，例如SUM，COUNT等。如果存在对字符串操作的聚类函数，我们应该用如下的SQL语句来实现这个结果。

SELECT CONCAT(City)

From Cities

GROUP BY Province

非常遗憾，我们从SQL中没有找到这样的聚类函数。因此我们需要另外需要方法来解决这个问题。类似的问题在如下帖子中也有讨论，http://www.dwww.cn/News/2008-6/2008625189397497.shtml。但其中的三个方法都不完美。

1）修改表结果：需要事先预测结果的内容与长度

2）多层JOIN：需要事先知道结果的内容与长度

3）SQL Server CLR扩展：支撑的字符数上限为8000，并且需要扩展代码

其实，为了寻找一个更简单的方法，我们可以采用SQL语句中的递归语句。

WITH [TableName] AS

{

<anchor_member>

UNIONALL

<recursive_member>

}

<out_query_against_CTE>

备注：参考《Microsoft SQL Server 2012 T-SQL Fundamentals》第166页，http://it-ebooks.info/read/865/

首先，我们为所有城市生成一个索引，不同省份的索引相互之间没有关系。我们可以采用ROW_NUMBER()与Partition By实现。

SELECT [Province],[City],ROW_NUMBER() Over (partition by (Province) ORDER by (SELECT 0)) as RowsFROM [Test].[dbo].[Cities]

其次我们需要定义循环结构。

1）anchor_member：返回各省份的第一个城市，即Rows=1的所以省份与城市

2）Recursive_member：找到每个省份“Rows=已出现城市的Rows+1”的城市，即该省份其他城市按次序递归。

WITH CC AS(	Select Province, convert(nvarchar(max),[City]) as [City], Rows from 	(		SELECT 		[Province]		,[City]		,ROW_NUMBER() Over (partition by (Province) ORDER by (SELECT 0)) as Rows		FROM [Test].[dbo].[Cities]	) as [City]	where Rows = 1	UNION ALL	Select CC.Province, convert(nvarchar(max),CC.[City]+','+C.[City]) as [City],C.Rows as n from CC JOIN 	(		SELECT 		[Province]		,[City]		,ROW_NUMBER() Over (partition by (Province) ORDER by (SELECT 0)) as Rows		FROM [Test].[dbo].[Cities]	) as C ON CC.Rows + 1 = C.Rows and C.Province = CC.Province)

Select * From CC

可以发现结果的内容多于我们希望得到的内容。Rows决定了该记录中包含的城市数目。我们希望得到的结果应该是每个省份所有的城市，因此Rows应当是每个省份中的最大值。因此SELECT语句更改如下：

Select Province, City from CC as t1 where Rows = (Select Max(Rows) from CC as t2 where t2.Province = t1.Province)