sql server 2000:执行连接聚合子查询的思想

看看这些文章:

http://dataeducation.com/rowset-string-concatenation-which-method-is-best/

http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/ (请参阅响应中的Phil Factor交叉连接解决方案-它将在SQL Server 2000中工作)

显然,在sql server 2005中,for xml技巧是最简单、最灵活、通常也是最有效的。

至于为每一行返回一个行集,如果出于某种原因仍要这样做,则可以在存储过程中这样做,但客户端需要使用第一个行集中的所有行,然后转到下一个行集并将其与第一个行集中的第一行相关联,等等。将需要打开它作为第一个行集返回的同一个集上的游标,并按顺序运行多个选择以生成所有子行集。这是我的一种技巧,但只有在 所有 实际需要的数据(例如,在完全填充的树视图中)。

不管人们怎么说,在客户端做这件事通常是对带宽的极大浪费,因为返回所有行并在客户端进行循环和中断意味着在每一行的开头传输大量相同的列只是为了得到不断变化的列在行的末尾。

无论你做什么,都应该是 知情决定 基于 你的 用例。

我尝试了三种方法来解决这个问题,一种贴在这里的方法,activex脚本和udf函数。

对我来说,最有效的脚本(速度方面)是运行多个查询以获取额外数据的奇异axtive-x脚本。

udf平均花了22分钟来转换,subquery方法(贴在这里)花了大约5米,activex脚本花了4米30,这让我非常恼火,因为这是我希望丢弃的脚本。我得看看能不能在别的地方提高效率。

我认为额外的30s被tempdb用来存储数据,因为我的脚本需要一个order by。

值得注意的是,我正在收集大量的文本数据。

你也可以看看这个 script . 这基本上就是cade roux在他的文章中提到的交叉连接方法。

上面的方法看起来非常干净:您必须首先创建一个视图,然后根据视图中的值创建一个语句。您可以在代码中动态构建第二个sql语句,因此应该直接使用它。

我不确定这是否适用于SQL Server 2000,但您可以尝试:

--combine parent and child, children are CSV onto parent row
CREATE TABLE #TableA (RowID int, Value1 varchar(5), Value2 varchar(5))
INSERT INTO #TableA VALUES (1,'aaaaa','A')
INSERT INTO #TableA VALUES (2,'bbbbb','B')
INSERT INTO #TableA VALUES (3,'ccccc','C')

CREATE TABLE #TableB (RowID int, TypeOf varchar(10))
INSERT INTO #TableB VALUES (1,'wood')
INSERT INTO #TableB VALUES (2,'wood')
INSERT INTO #TableB VALUES (2,'steel')
INSERT INTO #TableB VALUES (2,'rock')
INSERT INTO #TableB VALUES (3,'plastic')
INSERT INTO #TableB VALUES (3,'paper')


SELECT
    a.*,dt.CombinedValue
    FROM #TableA        a
        LEFT OUTER JOIN (SELECT
                             c1.RowID
                                 ,STUFF(
                                          (SELECT
                                               ', ' + TypeOf
                                               FROM (SELECT
                                                         a.RowID,a.Value1,a.Value2,b.TypeOf
                                                         FROM #TableA                 a
                                                             LEFT OUTER JOIN #TableB  b ON a.RowID=b.RowID
                                                    ) c2
                                               WHERE c2.rowid=c1.rowid
                                               ORDER BY c1.RowID, TypeOf
                                               FOR XML PATH('') 
                                          )
                                          ,1,2, ''
                                       ) AS CombinedValue
                             FROM (SELECT
                                       a.RowID,a.Value1,a.Value2,b.TypeOf
                                       FROM #TableA                 a
                                           LEFT OUTER JOIN #TableB  b ON a.RowID=b.RowID
                                  ) c1
                             GROUP BY RowID
                        ) dt ON a.RowID=dt.RowID

来自SQL Server 2005的输出:

RowID       Value1 Value2 CombinedValue
----------- ------ ------ ------------------
1           aaaaa  A      wood
2           bbbbb  B      rock, steel, wood
3           ccccc  C      paper, plastic

(3 row(s) affected)

编辑 将for xml path替换为for xml raw的查询,因此这应该适用于SQL Server 2000

SELECT
    a.*,dt.CombinedValue
    FROM #TableA        a
        LEFT OUTER JOIN (SELECT
                             c1.RowID
                                 ,STUFF(REPLACE(REPLACE(
                                          (SELECT 
                                               ', ' + TypeOf as value
                                               FROM (SELECT
                                                         a.RowID,a.Value1,a.Value2,b.TypeOf
                                                         FROM #TableA                 a
                                                             LEFT OUTER JOIN #TableB  b ON a.RowID=b.RowID
                                                    ) c2
                                               WHERE c2.rowid=c1.rowid
                                               ORDER BY c1.RowID, TypeOf
                                               FOR XML RAW
                                          )
                                         ,'<row value="',''),'"/>','')
                                   , 1, 2, '') AS CombinedValue
                             FROM (SELECT
                                       a.RowID,a.Value1,a.Value2,b.TypeOf
                                       FROM #TableA                 a
                                           LEFT OUTER JOIN #TableB  b ON a.RowID=b.RowID
                                  ) c1
                             GROUP BY RowID
                        ) dt ON a.RowID=dt.RowID

输出,与原始查询相同