MySQL GROUP_CONCAT() 在查询中的 LIKE 约束后产生部分答案

Question

select b.book_id,isbn,title,description,book_count,group_concat(concat_ws(' ',a.firstname,a.middlename,a.lastname)) as authors 
from book b, author a, author_book ab 
where b.book_id = ab.book_id and a.author_id = ab.author_id 
and (a.firstname like '%abc%') group by isbn;

以上代码产生，

1001 | 1234567890123 | C Programming | Good book for beginners |         10 | abcd

但正确答案是多个作者，(包括 efgh、ijkl 作者)

1001 | 1234567890123 | C Programming | Good book for beginners |         10 | abcd, efgh, ijkl
1002 | 1234567890111 | Java | Good book for Java Programmers |          5 | xyz, uvw
...
...

这是在没有使用 LIKE 约束的情况下实现的，

select b.book_id,isbn,title,description,book_count,group_concat(concat_ws(' ',a.firstname,a.middlename,a.lastname)) as authors 
from book b, author a, author_book ab 
where b.book_id = ab.book_id and a.author_id = ab.author_id group by isbn;

如何达到预期的输出？

Answer 1

其他作者被删除，因为即使在您使用他们来选择您想要的书籍之后，您对作者姓名的限制仍然适用。

使用您当前的作者过滤器返回图书 ID 列表，然后将其用作子查询 WHERE bookId IN (SELECT ...) 来过滤您想要的图书，然后再次加入author_book 和 author 获取所选书籍的所有作者。

尝试这样的事情:

select  b.book_id,isbn,title,description,book_count,
 group_concat(concat_ws(' ',a.firstname,a.middlename,a.lastname)) as authors 
from book b, author a, author_book ab 
where 
 b.book_id = ab.book_id
 and a.author_id = ab.author_id 
 and b.book_id IN (
  SELECT ab.book_id 
  from author a, author_book ab 
  where a.author_id = ab.author_id  and (a.firstname like '%abc%')
)
group by isbn;