题解 | #列表 A,B中，对B中b1,b2对应同一个A中的a1的情况计数#

本题是比较经典的运用题，现实工作中比较常见，思路和方法有很多，以下仅供参考：

方法一：使用group_concat函数，找出由author_id分组合并issue_type后的字符串中同时包含'Education'和'Career'的人数。需要使用locate函数，当字符串不存在于给定的字符中时，结果返回0

select count(*) from
(select group_concat(distinct issue_type) s
from issue_tb ib,answer_tb at
where ib.issue_id=at.issue_id
group by author_id
having locate('Education',s)!=0 and locate('Career',s)!=0)a

方法二：使用with as创建临时表，从临时表中分别选出包含'Education'与'Career'的author_id，将两个author_id联合比较即可得出同时参加的人数

with x as (select distinct author_id,issue_type from 
issue_tb ib,answer_tb at
where ib.issue_id=at.issue_id)
select count(*) num from
(select distinct author_id s1 from x
where issue_type='Education')a join 
(select distinct author_id s2 from x
where issue_type='Career')b
on s1=s2

方法三：先找进行issue_type,author_id分组查询，记为表a，其中issue_type为'Education'或者'Career'；再将表a的author_id进行分组计数，再选择计数大于2的author_id进行计数即可

select count(*) num from
(select author_id,count(*) s from(
select issue_type,at.author_id from answer_tb at,issue_tb it
where at.issue_id=it.issue_id and (issue_type='Education' or issue_type='Career')
group by issue_type,author_id) a
group by author_id
having s>1)b

方法四：选出issue_type为'Career'的distinct(author_id)，再以子查询中issue_type为'Education'限定author_id，再进行count即可

select count(distinct(author_id)) num
from answer_tb at1 ,issue_tb it
where at1.issue_id=it.issue_id and issue_type='Career' and
at1.author_id in
(select distinct(author_id) from answer_tb at,issue_tb it
where at.issue_id=it.issue_id and issue_type='Education')