将列值合并到许多到许多关系联接中

I have two tables, Books and Authors, with many-to-many relationship between them through a third table called book_authors, i am trying to list all the books with the authors for each book using an inner join in order to display them in a DataList Control, but the join is causing several duplicate rows, because each book may have many authors, so there will be a row for each author.
Example:

我有两个表,书籍和作者,与它们之间的多对多关系通过第三表称为book_authors,我试图列出所有书每本书的作者使用内连接为了显示他们在DataList控制,但加入导致几个重复的行,因为每本书可能有许多作者,所以会有每个作者一行。例子:

book_title           author  
b1                    a1  
b1                    a2

What is the best way to fix this so it becomes:

解决这个问题的最好办法是什么呢?

book_title                author  
b1                        a1, a2

3 个解决方案

#1

Maybe something like this:

也许是这样的:

SELECT
    Books.book_title,
    STUFF
    (
        (
            SELECT 
                ',' +author
            FROM
                book_authors
                JOIN Authors
                    ON book_authors.authorId=Authors.authorId
            WHERE
                book_authors.bookId=Books.bookid
            FOR XML PATH('')
        )
    ,1,1,'')
FROM
    Books

EDIT

编辑

It is hard to say with out you data. Does this work:

没有你的数据很难说。这工作:

DECLARE @Table1 TABLE(ID INT)
DECLARE @Table2 TABLE(Name varchar(100),ID INT)

INSERT INTO @Table1 VALUES(1),(2)
INSERT INTO @Table2 VALUES('test1',1),('test2',1),('test3',2),('test4',2)

SELECT
    t1.ID,
    STUFF
    (
        (
            SELECT 
                ',' +t2.Name
            FROM
                @Table2 AS t2
            WHERE
                t1.ID=t2.ID
            FOR XML PATH('')
        )
    ,1,1,'')
FROM
    @Table1 AS t1

#2

If you want to do it purely in SQL then you will need a sub-query that takes the book id and yeilds a list of authors (csv if thats how you want it) as a result. Use that sub-query within another query that for each unique bookid it returns the book_title and author list.

如果您希望完全使用SQL，那么您将需要一个子查询，该子查询获取图书id和yeilds的一个列表(如果您想要它的话)。在另一个查询中使用子查询，每个唯一的bookid都返回book_title和author列表。

If you don't mind not using pure SQL, I'd say just iterate through the DataList you are currently getting and make a map-type structure (book_title -> authorList) adding the authors as you go through.

如果您不介意不使用纯SQL，那么我建议您只需遍历当前获得的数据列表，并创建一个地图类型的结构(book_title -> authorList)，并在执行过程中添加作者。

The best choice depends on the scope of how your are using this, but generally I'd say the sql route is the way to go.

最好的选择取决于您如何使用它的范围，但通常我认为sql路由是最好的选择。

#3

What you want to do is string aggregate concatenation. There are some very good posts on this subject.

您要做的是字符串聚合连接。在这个问题上有一些很好的文章。

Here is an alternative, that might work easily in your case, since books do not have too many authors:

这里有另一种选择，在你的情况下可能很容易，因为书中没有太多的作者:

select b.name,
       (max(case when authornum = 1 then author else '' end) +
        max(case when authornum = 2 then ', '+ author else '' end) +
        max(case when authornum = 3 then ', '+ author else '' end)
       ) as authors
 from (select ba.*,
              row_number() over (partition by bookid order by authorid) as authornum
       from BookAuthors ba
      ) bajoin
      Authors a
      on a.authorid = ba.authorid join
      Books b
      on b.bookid = ba.bookid
 group by b.name

You simply need to be sure that you include enough authors in the select statement.

您只需要确保在select语句中包含足够的作者。

#1