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/

这个语句可以定义一个表，这个表首先返回anchor_member中的内容；接着递归地返回recursive_member中的内容。

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

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

语句结果为：

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

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

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

<pre name="code" class="sql">WITH OrderedTable AS
(
	SELECT 
	[Province]
	,[City]
	,ROW_NUMBER() Over (partition by (Province) ORDER by (SELECT 0)) as Rows
	FROM [Test].[dbo].[Cities]
),
CC AS
(
	Select Province, convert(nvarchar(max),[City]) as [City], Rows
	from OrderedTable 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 OrderedTable 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)

最终，我们得到想要的结果：

还有不少方法可以参考下述文章：

http://blog.csdn.net/grandtree/article/details/4136339