mysql - GROUP_CONCAT - WHERE 条件

Question

select group_concat(bookgenre), author_authorID
from Written
group by author_authorID;

给我

表格

group_conat(bookgenre)      author_authorID
Fiction                     123450
Fiction, History            123451
History                     123457
Sci-Fi                      123458
Fiction                     123459

不知道怎么生成

group_concat(bookgenre)     author_authorID
Fiction                     123450
Fiction                     123459

当我添加条件时

where "group_concat(bookgenre)" like 'Fiction'

结果返回 0 行

Answer 1

你可以这样做

select group_concat(bookgenre), author_authorID
from Written
group by author_authorID;
having count(*) = sum(bookgenre = 'Fiction')

或者使用having子句过滤聚合函数的结果

select group_concat(bookgenre), author_authorID
from Written
group by author_authorID;
having group_concat(bookgenre) = 'Fiction'