MySQL 过滤/筛选 LEFT JOIN 派生表中的聚合的字段

碰到了一个需求，需要判断一个由left join的表（派生表）的字段（列）聚合而成的字段中是否包含有某个字符串。
查询了网上的一些资料，发现基本没有说到怎么使用WHERE对LEFT JOIN的右表聚合出的字段进行筛选。但是最后也是还到了一个比较巧妙的办法对其进行筛选。记录一下。
具体如下：

需要判断interests中是否含有某个interest。

1.数据库中的相应表

数据表: （ps:只截取了本需求中有用的部分）
cp_interests:(兴趣表)

cp_signups:(报名表)

cp_sighups_interests:(报名-兴趣对应表)

2.需求

需要根据报名ID找出每个报名人员的兴趣并合并成一条数据，并且需要兴趣中含有某个或某几个兴趣。

未筛选的sql语句如下：

SELECT
cp_signups.id,
GROUP_CONCAT( DISTINCT cp_interests.NAME ) as interests
FROM
cp_signups
LEFT JOIN ( SELECT id, NAME FROM cp_interests ) cp_interests ON cp_interests.id IN ( SELECT interest_id FROM cp_signups_interests WHERE signup_id = cp_signups.id )
GROUP BY
cp_signups.id

3.实现

对于这个需求，如果是一般的字符串字段，只需要在 WHERE 中对字段使用 LIKE 筛选以下就可以很简单的解决需求。
但在这个情景下，需要判断的字段是 LEFT JOIN 的表中的（主表不能改变，因为有可能完整的需求中不只有这一个聚合出字段需要判断），而对于派生表的筛选一般都是使用ON。但是，ON只会筛选加上的表的数据，不会筛选总表的数据。也就是说，如果左表的某行数据在筛选之后的右表没有对应的数据，则会设置为NULL。(红色语句为新加上的筛选条件)

sql语句：

SELECT
cp_signups.id,
GROUP_CONCAT( DISTINCT cp_interests.NAME ) AS interests
FROM
cp_signups
LEFT JOIN ( SELECT id, NAME FROM cp_interests ) cp_interests ON cp_interests.id IN ( SELECT interest_id FROM cp_signups_interests WHERE signup_id = cp_signups.id ) AND cp_interests.NAME = "聊天交友"
GROUP BY
cp_signups.id

结果：

可以看到不符合条件的数据仅仅是将interests字段设为了Null，并且还有个弊端就是其他的兴趣也没有了只剩下了一个兴趣。所以不能直接用ON进行判断。

那么既然要对总表中的数据进行筛选，很容易就想到应该在WHERE中增加判断条件。并且如果是在WHERE中增加条件，上述的另一个弊端也可以解决。

sql语句：

SELECT
cp_signups.id,
GROUP_CONCAT( DISTINCT cp_interests.NAME ) AS interests
FROM
cp_signups
LEFT JOIN ( SELECT id, NAME FROM cp_interests ) cp_interests ON cp_interests.id IN ( SELECT interest_id FROM cp_signups_interests WHERE signup_id = cp_signups.id )
WHERE interests LIKE "%聊天交友%"
(或者 WHERE cp_interests.interests LIKE "%聊天交友%")
GROUP BY
cp_signups.id

理论上这样做是可以解决需求的，但是事实上还会出一个问题：

1054 - Unknown column ‘interests’ in ‘where clause’

它报错了。可能是因为该字段是一个虚拟字段的原因，并不能将interests字段放在where中访问。并且因为这个字段是由GROUP_CONCAT函数聚合出来的，并没有表名，因此也不能指定表名去访问这个字段。

最后，找到了一个比较巧妙的方法来解决问题

sql语句如下：

SELECT tmp.* FROM cp_signups LEFT JOIN (
SELECT
cp_signups.id,
GROUP_CONCAT( DISTINCT cp_interests.NAME ) AS interests
FROM
cp_signups
LEFT JOIN ( SELECT id, NAME FROM cp_interests ) cp_interests ON cp_interests.id IN ( SELECT interest_id FROM cp_signups_interests WHERE signup_id = cp_signups.id )
GROUP BY
cp_signups.id
) tmp ON tmp.id = cp_signups.id WHERE tmp.interests LIKE "%聊天交友%"

这个方法把刚才查到的整个表作为LEFT JOIN的派生表，并且取了一个别名tmp。为了防止字段冗余主表中并没有select字段。最后，因为有了一个表明tmp，在WHERE中就能够访问到interests字段了。

但是这个方法还是略显笨拙。如果有更好的解决问题的办法，欢迎并感谢您的分享。