查询显示关联的列表中的许多一对多的关系
我有两个表,书和标签,和书籍所使用的关联表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 /名称,可以硬编码它们进入你的查询,恐怕答案是否定的。
如果你知道的标签为一本书的最大数量,你可以使用一个支点,让他们在同一行,然后使用COALESCE,但在一般情况下,我不相信有。
最干净的解决办法可能是使用自定义的C#CLR聚合函数。我们发现这很有效。你可以找到在http://dotnet-enthusiast.blogspot.com/2007/05/user-defined-aggregate-function-in-sql.html
创建此说明我怕我不知道该标签ID是什么 - 这是该报告是有什么:) – 2008-10-06 14:23:30