Question

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_title           author  
b1                    a1  
b1                    a2

解决这一问题的最佳途径是:

book_title                author  
b1                        a1, a2

Answer 1

或许可以这样说:

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

http://www.ohchr.org。

很难说一下你的数据。这项工作是否:

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

Answer 2

如果你只想在Kingk做,那么你将需要一个分局,该分局将登上该书,并据此编制一份作者名单(如果你想这样做的话)。在另一个问询中使用这一次频率,即,每个独有的书目上填写的书名和作者名单。

如果你不使用纯粹的LQ,我只是说,通过数据管理员,你目前正在获得并制作一张地图型结构(书籍——标题-和”;作者List),在你接手时添加作者。

最好的选择取决于你如何利用这一途径的范围,但一般来说,我可以说, s路是走路。

Answer 3

你们想要做的是扼杀总分类。在这个问题上有一些很好的职位。

这里是一种替代办法,在你的案件中可以轻松工作,因为书本没有太多作者:

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

你们完全需要确保你在选择发言中包含足够的作者。

友情链接