admin

加入2对多关系

sql

我正在尝试加入3个表,但是却得到了一些不寻常的结果,例如,有3个类别的书,从书夹1中出现了3条记录,如何避免这种情况?

表=书-书本-作者-标题

表格=类别书籍-书本-类别ID

表=类别-categoryid-categorydesc

当我使用以下方式加入表格时:

SELECT book.bookid, book.author, book.title, category.categorydesc
FROM book
JOIN bookscategories ON book.bookid = bookscategories.bookid
JOIN category ON bookscategories.categoryid = category.categoryid

阅读 41

收藏
2021-06-07

共1个答案

admin

很简单,使用按书名分组将结果限制为每本书一行。您可以使用group_concat仍在CSV列表中查看所有类别。

SELECT book.bookid, book.author, book.title, group_concat(category.categorydesc)
FROM book
JOIN bookscategories ON book.bookid = bookscategories.bookid
JOIN category ON bookscategories.categoryid = category.categoryid
GROUP BY book.book_id

参见:http :
//dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-
concat

2021-06-07