<i id='uxumY'><tr id='uxumY'><dt id='uxumY'><q id='uxumY'><span id='uxumY'><b id='uxumY'><form id='uxumY'><ins id='uxumY'></ins><ul id='uxumY'></ul><sub id='uxumY'></sub></form><legend id='uxumY'></legend><bdo id='uxumY'><pre id='uxumY'><center id='uxumY'></center></pre></bdo></b><th id='uxumY'></th></span></q></dt></tr></i><div id='uxumY'><tfoot id='uxumY'></tfoot><dl id='uxumY'><fieldset id='uxumY'></fieldset></dl></div>
    <legend id='uxumY'><style id='uxumY'><dir id='uxumY'><q id='uxumY'></q></dir></style></legend>

  1. <tfoot id='uxumY'></tfoot>
      • <bdo id='uxumY'></bdo><ul id='uxumY'></ul>

      <small id='uxumY'></small><noframes id='uxumY'>

      连接/聚合字符串的最佳方式

      Optimal way to concatenate/aggregate strings(连接/聚合字符串的最佳方式)
      • <i id='LXOPZ'><tr id='LXOPZ'><dt id='LXOPZ'><q id='LXOPZ'><span id='LXOPZ'><b id='LXOPZ'><form id='LXOPZ'><ins id='LXOPZ'></ins><ul id='LXOPZ'></ul><sub id='LXOPZ'></sub></form><legend id='LXOPZ'></legend><bdo id='LXOPZ'><pre id='LXOPZ'><center id='LXOPZ'></center></pre></bdo></b><th id='LXOPZ'></th></span></q></dt></tr></i><div id='LXOPZ'><tfoot id='LXOPZ'></tfoot><dl id='LXOPZ'><fieldset id='LXOPZ'></fieldset></dl></div>
          <tbody id='LXOPZ'></tbody>

            <bdo id='LXOPZ'></bdo><ul id='LXOPZ'></ul>

            <small id='LXOPZ'></small><noframes id='LXOPZ'>

          • <legend id='LXOPZ'><style id='LXOPZ'><dir id='LXOPZ'><q id='LXOPZ'></q></dir></style></legend>
            <tfoot id='LXOPZ'></tfoot>
              1. 本文介绍了连接/聚合字符串的最佳方式的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                问题描述

                我正在寻找一种将不同行中的字符串聚合为一行的方法.我希望在许多不同的地方这样做,所以有一个功能来促进这一点会很好.我已经尝试过使用 COALESCEFOR XML 的解决方案,但他们只是不适合我.

                I'm finding a way to aggregate strings from different rows into a single row. I'm looking to do this in many different places, so having a function to facilitate this would be nice. I've tried solutions using COALESCE and FOR XML, but they just don't cut it for me.

                字符串聚合会做这样的事情:

                String aggregation would do something like this:

                id | Name                    Result: id | Names
                -- - ----                            -- - -----
                1  | Matt                            1  | Matt, Rocks
                1  | Rocks                           2  | Stylus
                2  | Stylus
                

                我看过CLR 定义的聚合函数 作为 COALESCEFOR XML 的替代品,但显然 SQL Azure 确实可以不支持 CLR 定义的东西,这对我来说很痛苦,因为我知道能够使用它会为我解决很多问题.

                I've taken a look at CLR-defined aggregate functions as a replacement for COALESCE and FOR XML, but apparently SQL Azure does not support CLR-defined stuff, which is a pain for me because I know being able to use it would solve a whole lot of problems for me.

                是否有任何可能的解决方法或类似的最佳方法(可能不如 CLR 最佳,但是我会用我能得到的)来聚合我的东西?

                Is there any possible workaround, or similarly optimal method (which might not be as optimal as CLR, but hey I'll take what I can get) that I can use to aggregate my stuff?

                推荐答案

                解决方案

                optimal 的定义可能会有所不同,但这里介绍了如何使用常规 Transact SQL 连接来自不同行的字符串,这在 Azure 中应该可以正常工作.

                The definition of optimal can vary, but here's how to concatenate strings from different rows using regular Transact SQL, which should work fine in Azure.

                ;WITH Partitioned AS
                (
                    SELECT 
                        ID,
                        Name,
                        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Name) AS NameNumber,
                        COUNT(*) OVER (PARTITION BY ID) AS NameCount
                    FROM dbo.SourceTable
                ),
                Concatenated AS
                (
                    SELECT 
                        ID, 
                        CAST(Name AS nvarchar) AS FullName, 
                        Name, 
                        NameNumber, 
                        NameCount 
                    FROM Partitioned 
                    WHERE NameNumber = 1
                
                    UNION ALL
                
                    SELECT 
                        P.ID, 
                        CAST(C.FullName + ', ' + P.Name AS nvarchar), 
                        P.Name, 
                        P.NameNumber, 
                        P.NameCount
                    FROM Partitioned AS P
                        INNER JOIN Concatenated AS C 
                                ON P.ID = C.ID 
                                AND P.NameNumber = C.NameNumber + 1
                )
                SELECT 
                    ID,
                    FullName
                FROM Concatenated
                WHERE NameNumber = NameCount
                

                说明

                该方法归结为三个步骤:

                The approach boils down to three steps:

                1. 使用OVERPARTITION 对行进行编号,并根据需要对它们进行串联排序.结果是Partitioned CTE.我们保留每个分区中的行数,以便稍后过滤结果.

                1. Number the rows using OVER and PARTITION grouping and ordering them as needed for the concatenation. The result is Partitioned CTE. We keep counts of rows in each partition to filter the results later.

                使用递归 CTE(Concatenated)遍历行号(NameNumber 列),将 Name 值添加到 FullName 列.

                Using recursive CTE (Concatenated) iterate through the row numbers (NameNumber column) adding Name values to FullName column.

                过滤除NameNumber 最高的所有结果.

                Filter out all results but the ones with the highest NameNumber.

                请记住,为了使此查询可预测,必须同时定义分组(例如,在您的场景中,具有相同 ID 的行被连接)和排序(我假设您只需在串联之前按字母顺序对字符串进行排序).

                Please keep in mind that in order to make this query predictable one has to define both grouping (for example, in your scenario rows with the same ID are concatenated) and sorting (I assumed that you simply sort the string alphabetically before concatenation).

                我已经使用以下数据在 SQL Server 2012 上快速测试了该解决方案:

                I've quickly tested the solution on SQL Server 2012 with the following data:

                INSERT dbo.SourceTable (ID, Name)
                VALUES 
                (1, 'Matt'),
                (1, 'Rocks'),
                (2, 'Stylus'),
                (3, 'Foo'),
                (3, 'Bar'),
                (3, 'Baz')
                

                查询结果:

                ID          FullName
                ----------- ------------------------------
                2           Stylus
                3           Bar, Baz, Foo
                1           Matt, Rocks
                

                这篇关于连接/聚合字符串的最佳方式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

                相关文档推荐

                Select n random rows from SQL Server table(从 SQL Server 表中随机选择 n 行)
                SQL query to select dates between two dates(用于选择两个日期之间的日期的 SQL 查询)
                How can I delete using INNER JOIN with SQL Server?(如何在 SQL Server 中使用 INNER JOIN 进行删除?)
                Table Naming Dilemma: Singular vs. Plural Names(表命名困境:单数与复数名称)
                INSERT statement conflicted with the FOREIGN KEY constraint - SQL Server(INSERT 语句与 FOREIGN KEY 约束冲突 - SQL Server)
                How ROWNUM works in pagination query?(ROWNUM 如何在分页查询中工作?)
                <tfoot id='3BPql'></tfoot>
                  <tbody id='3BPql'></tbody>
                  <i id='3BPql'><tr id='3BPql'><dt id='3BPql'><q id='3BPql'><span id='3BPql'><b id='3BPql'><form id='3BPql'><ins id='3BPql'></ins><ul id='3BPql'></ul><sub id='3BPql'></sub></form><legend id='3BPql'></legend><bdo id='3BPql'><pre id='3BPql'><center id='3BPql'></center></pre></bdo></b><th id='3BPql'></th></span></q></dt></tr></i><div id='3BPql'><tfoot id='3BPql'></tfoot><dl id='3BPql'><fieldset id='3BPql'></fieldset></dl></div>
                  1. <legend id='3BPql'><style id='3BPql'><dir id='3BPql'><q id='3BPql'></q></dir></style></legend>

                    • <small id='3BPql'></small><noframes id='3BPql'>

                          <bdo id='3BPql'></bdo><ul id='3BPql'></ul>