查询显示关联的列表中的许多一对多的关系

问题描述:

我有两个表,标签,和书籍所使用的关联表BookTag标记。我想创建一个包含书籍列表的报告,并为每本书列出书籍标签的列表。标签ID就足够了,标签名称不是必需的。查询显示关联的列表中的许多一对多的关系

例子:

Book table: 
Book ID | Book Name 
28  | Dracula 

BookTag table: 
Book ID | Tag ID 
28  | 101 
28  | 102 

在我的报告,我想表明的书#28有标签101和102:

Book ID | Book Name | Tags 
28  | Dracula | 101, 102 

有没有办法做到这一点在 - 行,而不必诉诸于功能或存储过程?我正在使用SQL Server 2005.

请注意,在Combine multiple results in a subquery into a single comma-separated value中已经提出了同样的问题,但解决方案涉及创建一个函数。我问是否有办法解决这个问题,而不必创建一个函数或存储过程。

你几乎可以做到这一点。我没有解决的唯一问题是逗号分隔符。以下是对使用空格分隔标签的类似结构的查询。

SELECT em.Code, 
    (SELECT et.Name + ' ' AS 'data()' 
     FROM tblEmployeeTag et 
      JOIN tblEmployeeTagAssignment eta ON et.Id = eta.EmployeeTag_Id AND eta.Employee_Id = em.id 
    FOR XML PATH('')) AS Tags 
FROM tblEmployee em 

编辑:

下面是使用你的表,并使用逗号分隔符的完整版本:

SELECT bk.Id AS BookId, 
     bk.Name AS BookName, 
    REPLACE((SELECT LTRIM(STR(bt.TagId)) + ', ' AS 'data()' 
     FROM BookTag bt 
     WHERE bt.BookId = bk.Id   
     FOR XML PATH('')) + 'x', ', x','') AS Tags 
FROM Book bk 

我想以供将来参考我要简要介绍一下这是怎么回事。 'data()'列名是一个与FOR XML PATH语句相关的特殊值。它会使XML文档呈现出来,就像在结果XML的根节点上执行.InnerText一样。
的REPLACE语句是一招,去掉后面的逗号。通过在标签列表的末尾添加一个唯一的字符(我随机选择了'x'),我可以搜索逗号空格字符并将其替换为空字符串。这使我可以砍掉最后一个逗号。这假定你永远不会在你的标签中拥有那个字符序列。

除非你知道什么标签ID /名称,可以硬编码它们进入你的查询,恐怕答案是否定的。

+0

创建此说明我怕我不知道该标签ID是什么 - 这是该报告是有什么:) – 2008-10-06 14:23:30

如果你知道的标签为一本书的最大数量,你可以使用一个支点,让他们在同一行,然后使用COALESCE,但在一般情况下,我不相信有。

最干净的解决办法可能是使用自定义的C#CLR聚合函数。我们发现这很有效。你可以找到在http://dotnet-enthusiast.blogspot.com/2007/05/user-defined-aggregate-function-in-sql.html