mysql - 如何用SQL语句包含同时包含多个标签？

Question

现在有三张数据表， 一张是书籍表， 一张是书籍的标签表， 由于数据与标签是多对多的关系， 所以还有一张中间表。 大概的结构如下：

书籍表 books

|id|name|
|001|BOOK001|
|002|BOOK002|

标签表 labels

|id|name|
|001|LABEL001|
|002|LABEL002|

中间表 book_label_maps

|id|book_id|label_id|
|001|001|001|
|002|001|002|
|003|002|001|

那么问题来了， 我怎么才能找出标记为LABEL001和LABEL002的呢？ 这里也就是找出BOOK001.

PS. 由于在真实查询过程中标签的数量不固定， 所以， 最好能够将查询的LABEL id全都放到一个地方。

例如： select * from books where otherquery(laebl001, label002)

目前的解决办法（PHP）：

$query = 'SELECT * FROM books WHERE ';
foreach( $labels as $index => $laeblID ) {
  $laebls[$index] => 'EXISTS (SELECT * FROM book_label_maps WHERE book_id = book.id AND laebl_id='.$laeblID.' )'
}
$laebls = implode(' AND ', $laebls);
$query .= $labels;

结果大概是这样的：

SELECT * FROM books 
WHERE 
  EXISTS (SELECT * FROM book_label_maps WHERE book_id = book.id AND label_id="001" ) 
  AND 
  EXISTS (SELECT * FROM book_label_maps WHERE book_id = book.id AND label_id="002")

Answer 1

I just started learning SQL not long ago, but I happened to encounter another problem a few days ago. The solution may be more usable and better than the solution proposed by the questioner. In my opinion, even if the method proposed by the question is feasible, I feel it is not very efficient. For example: 100 books, each book has 10 tags, and the subquery under your WHERE will have to be checked 1,000 times. Well, let’s talk about the problems I encountered before. What I encountered before was the problem of merging rows. In your example, we need to merge labels based on BookID, which means the following effect is needed

   book_id|label_id
    001|001，002
    002|001

Then when querying this result set, you can query based on string matching such as "001,002". I use oracle, and post the SQL of the solution available in oracle:

SELECT bookID，LTRIM(MAX(SYS_CONNECT_BY_PATH(labelID,','))KEEP(DENSE_RANK LAST ORDER BY curr),',') AS labels
FROM (
SELECT labelID ,bookID
ROW_NUMBER() OVER (ORDER BY bookID)  curr ,
ROW_NUMBER() OVER (ORDER BY bookID) -1 prev 
FROM book_label_maps) 
GROUP BY bookID
CONNECT BY prev = PRIOR curr AND bookID=bookID START WITH curr=1

The above SQL statement can generate a table aggregated based on bookID through your MAPS table (I have not actually tested it). When I searched before, I saw that MYSQL also has its function for merging rows. You can check it out. If there are many needs such as "books containing several tags", you can create a view to generate a result set of merged rows, and then all such needs can be checked in the view, which saves trouble.