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

I have two tables, Book and Tag, and books are tagged using the association table BookTag. I want to create a report that contains a list of books, and for each book a list of the book's tags. Tag IDs will suffice, tag names are not necessary.

我有两个表，书和标签，书籍使用关联表BookTag标记。我想创建一个包含书籍列表的报告，并为每本书创建书籍标签的列表。标签ID就足够了，标签名称不是必需的。

Example:

例：

Book table:
Book ID | Book Name
28      | Dracula

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

In my report, I'd like to show that book #28 has the tags 101 and 102:

在我的报告中，我想表明第28本书有标签101和102：

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

Is there a way to do this in-line, without having to resort to functions or stored procedures? I am using SQL Server 2005.

有没有办法在线执行此操作，而无需借助函数或存储过程？我正在使用SQL Server 2005。

Please note that the same question already has been asked in Combine multiple results in a subquery into a single comma-separated value, but the solution involves creating a function. I am asking if there is a way to solve this without having to create a function or a stored procedure.

请注意，在将子查询中的多个结果合并为单个逗号分隔值时，已经询问过相同的问题，但解决方案涉及创建函数。我问是否有办法解决这个问题，而无需创建函数或存储过程。

4 个解决方案

#1

You can almost do it. The only problem I haven't resolved is the comma delimiter. Here is a query on a similar structure that separates the tags using a space.

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

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

Edit:

编辑：

Here is the complete version using your tables and using a comma delimiter:

以下是使用表格并使用逗号分隔符的完整版本：

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

I suppose for future reference I should explain a bit about what is going on. The 'data()' column name is a special value that is related to the FOR XML PATH statement. It causes the XML document to be rendered as if you did an .InnerText on the root node of the resulting XML.
The REPLACE statement is a trick to remove the trailing comma. By appending a unique character (I randomly chose 'x') to the end of the tag list I can search for comma-space-character and replace it with an empty string. That allows me to chop off just the last comma. This assumes that you are never going to have that sequence of characters in your tags.

我想，为了将来的参考，我应该解释一下发生了什么。 'data（）'列名是一个与FOR XML PATH语句相关的特殊值。它会使XML文档呈现，就像在生成的XML的根节点上执行.InnerText一样。 REPLACE语句是删除尾随逗号的技巧。通过在标记列表的末尾附加一个唯一字符（我随机选择“x”），我可以搜索逗号空格字符并用空字符串替换它。这让我可以删掉最后一个逗号。这假设您永远不会在标签中包含该字符序列。

#2

Unless you know what the tag ids/names are and can hard code them into your query, I'm afraid the answer is no.

除非您知道标签ID /名称是什么，并且可以将它们硬编码到您的查询中，否则我担心答案是否定的。

#3

If you knew the maximum number of tags for a book, you could use a pivot to get them to the same row and then use COALESCE, but in general, I don't believe there is.

如果你知道一本书的最大标签数量，你可以使用一个支点将它们带到同一行然后使用COALESCE，但总的来说，我不相信它。

#4

The cleanest solution is probably to use a custom C# CLR aggregate function. We have found that this works really well. You can find instructions for creating this at http://dotnet-enthusiast.blogspot.com/2007/05/user-defined-aggregate-function-in-sql.html

最干净的解决方案可能是使用自定义C＃CLR聚合函数。我们发现这很有效。您可以在http://dotnet-enthusiast.blogspot.com/2007/05/user-defined-aggregate-function-in-sql.html找到有关创建此说明的说明。

#1