代码之家  ›  专栏  ›  技术社区  ›  Marcus L

逗号分隔

  •  4
  • Marcus L  · 技术社区  · 16 年前

    我正在编写一个导出函数,在这里我需要将联系人导出到Excel,但我遇到了一个技术障碍——或者说,我的SQL技能差距更接近实际情况。;)

    我在数据库里有很多联系人。每个联系人可以有许多不同的角色,例如,联系人可以是C#开发人员和DBA,也可以是DBA和IT经理。这些表分为三个表,如下所示:

    -------------------   -------------------   -------------------
    *     Contact     *   *   ContactRole   *   *      Role       *
    -------------------   -------------------   -------------------
    * ID              *   * ContactID       *   * ID              *
    * Name            *   * RoleID          *   * Name            *
    * Address         *   -------------------   -------------------
    -------------------   
    

    不难理解。有一组联系人和一组角色。它们由各自ID上的ContactRole表连接。

    当我导出联系人时,我需要在导出中有一列,其中所有角色以逗号分隔,如 C# Developer, DBA DBA, IT-manager .

    数据来自SQLServer2005。

    7 回复  |  直到 16 年前
        1
  •  6
  •   van    16 年前

    因为您使用的是SQL Server 2005(如果幸运的话,您已经正确设置了所有XML设置),下面是您的简单SQL查询(纯SQL,没有函数):

    SELECT  c.ID, c.Name, c.Address, 
        (   SELECT      r.Name + ','
            FROM        "ContactRole" cr
            INNER JOIN  "Role" r
                    ON  cr.RoleID = r.ID
            WHERE       cr.ContactID = c.ID
            ORDER BY    r.ID --r.Name
            FOR XML PATH('')
        ) AS "Roles"
    FROM    "Contact" c
    

    要测试它是否适合您,只需执行以下整个代码段:

    WITH "Contact" (ID, Name, Address) AS (
                    SELECT 1, 'p1-no role', NULL
        UNION ALL   SELECT 2, 'p2-one role', NULL
        UNION ALL   SELECT 3, 'p3-two roles', NULL
    )
    , "Role" (ID, Name)AS (
                    SELECT 1, 'teacher'
        UNION ALL   SELECT 2, 'student'
    )
    , "ContactRole" (ContactID, RoleID) AS (
                    SELECT 2, 1
        UNION ALL   SELECT 3, 1
        UNION ALL   SELECT 3, 2
    )
    
    SELECT  c.ID, c.Name, c.Address, 
        (   SELECT      r.Name + ','
            FROM        "ContactRole" cr
            INNER JOIN  "Role" r
                    ON  cr.RoleID = r.ID
            WHERE       cr.ContactID = c.ID
            ORDER BY    r.ID --r.Name
            FOR XML PATH('')
        ) AS "Roles"
    FROM    "Contact" c
    

    您应该得到以下结果:

    ID          Name         Address     Roles
    ----------- ------------ ----------- ------------------
    1           p1-no role   NULL        NULL
    2           p2-one role  NULL        teacher,
    3           p3-two roles NULL        teacher,student,
    
        2
  •  5
  •   Marek Grzenkowicz    16 年前

    CLR用户定义的聚合 为了得到这样的结果。用户定义的聚合可以像用户定义的聚合一样调用(例如SUM或MAX),并且它不使用游标。

    using System;
    using System.Data;
    using Microsoft.SqlServer.Server;
    using System.Data.SqlTypes;
    using System.IO;
    using System.Text;
    
    [Serializable()]
    [SqlUserDefinedAggregate(
        Format.UserDefined,
        IsInvariantToNulls=true,
        IsInvariantToDuplicates=false,
        IsInvariantToOrder=false,
        MaxByteSize=8000)]
    public class Concat : IBinarySerialize
    {
        #region Private fields
        private string separator;
        private StringBuilder intermediateResult;
        #endregion
    
        #region IBinarySerialize members
        public void Read(BinaryReader r)
        {
            this.intermediateResult = new StringBuilder(r.ReadString());
        }
    
        public void Write(BinaryWriter w)
        {
            w.Write(this.intermediateResult.ToString());
        }
        #endregion
    
        #region Aggregation contract methods
        public void Init()
        {
            this.separator = ", ";
            this.intermediateResult = new StringBuilder();
        }
    
        public void Accumulate(SqlString pValue)
        {
            if (pValue.IsNull)
            {
                return;
            }
    
            if (this.intermediateResult.Length > 0)
            {
                this.intermediateResult.Append(this.separator);
            }
            this.intermediateResult.Append(pValue.Value);
        }
    
        public void Merge(Concat pOtherAggregate)
        {
            this.intermediateResult.Append(pOtherAggregate.intermediateResult);
        }
    
        public SqlString Terminate()
        {
            return this.intermediateResult.ToString();
        }
        #endregion
    }
    

    在里面 this posts

    我用了这个骨料 生产环境

        3
  •  4
  •   devio    16 年前

    试试这个

    declare @Roles nvarchar(max)
    
    select @Roles = case when @Roles is null then '' else @Roles + ', ' end + Role.Name
    from  Role
    inner join ContactRole on Role.ID = ContactRole.RoleID
    where ContactRole.ContactID = @ContactID
    
    select @Roles 
    

    更新:

    以上代码涵盖了单个联系人的功能。您可以使用参数@ContactID创建标量函数,并从

    Select Name, dbo.GetContactRoles(ID) From Contact
    
        4
  •  2
  •   NateJ Richard Berg    16 年前

    SELECT [<group field 1>], [<group field 2>], [etc...], (
        SELECT CAST([<field to list>] AS VARCHAR(MAX)) + 
            CASE WHEN (ROW_NUMBER() OVER (ORDER BY [<inner order-by REVERSED>]) = 1)
                THEN '' ELSE ',' END
            AS [text()]
        FROM [<inner table>]
        WHERE [<inner table join field>] = [<outer table join field>]
        AND [<inner conditions>]
        ORDER BY [<inner order-by>]
        FOR XML PATH('')) AS [<alias>]
    FROM [<outer table]
    WHERE [<outer conditions>]
    

    颠倒

        5
  •  1
  •   Ish    16 年前

    SQL查询:

    SELECT Contact.Name as cName, Role.Name as rName FROM Contact 
    JOIN ContactRole ON (Contact.ID==ContactRole.ContactID)
    JOIN Role ON  ON (Role.ID==ContactRole.RoleID)
    

    接下来继续应用程序逻辑

    forloop:
        array[ cName ] .= rName.', ';
    endforloop;
    
        6
  •  1
  •   Community CDub    8 年前

    编辑:根据从表重写为标量函数 devio's idea 所以,如果你喜欢这篇文章,请投他的票。

    create function dbo.getRole(
        @ContactId int)
    returns varchar(8000)
    as
    begin
    declare @Roles varchar(8000)
    
    select 
        @Roles = case when @Roles is null then '' else @Roles + ', ' end + Role.Name
    from Role
    inner join ContactRole on Role.ID = ContactRole.RoleID
    where ContactRole.ContactID = @ContactID
    
    return @Roles
    

    然后可以调用此函数来计算每个联系人的逗号分隔列表:

    SELECT c.id, c.name, dbo.getRole(ID) as Roles
    FROM Contact
    
        7
  •  0
  •   TheVillageIdiot    16 年前

    您可以编写一个函数,在传递联系人id时将角色作为逗号分隔的字符串输出。 然后在select语句中调用此函数:)

    例如,如果要获取客户以特定顺序订购的产品,可以使用以下代码:

        create function FetchProducts(@orderid int) returns varchar(1000)
        as
        begin
        declare prods cursor for select ProductName from products where 
                productid in (select ProductId from [Order Details] 
                  Where OrderId = @orderid)
    
        open prods
    
        declare @products  varchar(1000)
        declare @cp varchar(500)
        Select @products = ''
        fetch prods into @cp
    
        while @@fetch_status = 0
        begin
            SET @products = @products + ',' + @cp
            fetch prods into @cp
        end
    
        close prods
        deallocate prods
    
        return substring(@products, 2, len(@products)-1)
        end
    

    现在,您可以使用以下功能:

        select orderid, orderdate, dbo.FetchProducts(orderid) 
    from orders where customerid = 'BERGS'