某乎问答回答过教育类问题的用户里有多少用户回答过职场类问题

【难度】：简单
【分类】：子查询

分析思路

难点：

1.如何使用in子查询

（1）统计回答过教育类问题的用户

• [条件]：where issue_type = 'Education'

（2）统计回答过教育类问题的用户里有多少用户回答过职场类问题

• [条件]：where issue_type = 'Career'

• [使用]：in子查询

最终结果

select 查询结果 [用户数]
from 从哪张表中查询数据 [多表连接]
where 查询条件 [回答过职场类问题]

求解代码

方法一

in子查询

#统计回答过教育类问题的用户里有多少用户回答过职场类问题
select
    count(distinct author_id) as num
from answer_tb
join issue_tb using(issue_id)
where issue_type = 'Career'
and author_id in(
    #统计回答过教育类问题的用户
    select distinct
        author_id
    from answer_tb
    join issue_tb using(issue_id)
    where issue_type = 'Education'
)

方法一

with子句

with
    main as(
        #统计回答过教育类问题的用户
        select distinct
            author_id
        from answer_tb
        join issue_tb using(issue_id)
        where issue_type = 'Education'
    )
#统计回答过教育类问题的用户里有多少用户回答过职场类问题
select
    count(distinct author_id) as num
from answer_tb
join issue_tb using(issue_id)
join main using(author_id)
where issue_type = 'Career'

-- 该问题就是 求同时回答过教育类和职场类的用户数

select
count(1) as num
from (
    select
    an.author_id as author_id
    from answer_tb an
    inner join issue_tb iu
        on an.issue_id = iu.issue_id
        and iu.issue_type in ('Education', 'Career')
    group by an.author_id
    having count(distinct iu.issue_type)=2
) t
;

with t as (
    select 
        author_id
    from issue_tb i
    join answer_tb a
    on i.issue_id = a.issue_id
    where issue_type in ('Education','Career')
    group by author_id
    having count(distinct issue_type) = 2
)
select count(*) num from t

select count(distinct author_id)
from answer_tb join issue_tb using(issue_id)
where author_id in(select author_id 
                  from answer_tb join issue_tb using(issue_id) 
                  where issue_type='Education')
and issue_type='Career';

with t1 as( 
  select distinct author_id  #去除重复值
  from answer_tb
  join issue_tb using(issue_id)
  where issue_type = 'Education'
),  # 先选择出回答过教育部问题的用户
t2 as(  
  select distinct author_id 
  from answer_tb
  join issue_tb using(issue_id)
  where issue_type = 'Career')  #在选择回答过职业的用户
  
select count(author_id) as num
from t1
where author_id in (SELECT author_id FROM t2)

-- 庆幸的是：不是求回答过教育类问题的用户里有多少比例的用户回答过职场类问题
-- 导致这一题一下子变为简单题

with tmp1 as(
select i.issue_type,i.issue_id,a.author_id
from answer_tb a inner join issue_tb i
on a.issue_id=i.issue_id
)
,
education_author as(
    select distinct author_id from tmp1
    where issue_type="Education"
)
,
career_author as(
    select distinct author_id from tmp1
    where issue_type="Career"
)
select count(e.author_id) from education_author e inner join career_author c
on e.author_id=c.author_id

with t1 as (
select distinct author_id
from issue_tb
join answer_tb
using(issue_id)
where issue_type="Education"
),t2 as (
select distinct author_id
from issue_tb
join answer_tb
using(issue_id)
where issue_type="Career"   
)
select count(distinct author_id) as num
from t1 
join t2 
using(author_id)

两个集合的交集元素个数就是结果
select count(*) num
from
(
    select distinct author_id
    from answer_tb a
    join issue_tb i
    on a.issue_id = i.issue_id
    where i.issue_type = 'Education'
) a
inner join
(
    select distinct author_id
    from answer_tb a
    join issue_tb i
    on a.issue_id = i.issue_id
    where i.issue_type = 'Career'
) b
on a.author_id = b.author_id

-- 第一种
select
    count(distinct t1.author_id) as num
from answer_tb t1
    join answer_tb t2 using(author_id)
where t1.issue_id like 'E%' and t2.issue_id like 'C%'
;

-- 第二种
with
answer_info as 
(
    select
        an.issue_id
        , author_id
        , issue_type
    from answer_tb an
        left join issue_tb iss on an.issue_id = iss.issue_id
)
select
    count(distinct t1.author_id) as num
from
    (
        select
            author_id
        from answer_info
        where issue_type = 'Education'
    ) t1
    inner join
    (
        select
            author_id
        from answer_info
        where issue_type = 'Career'
    ) t2
    on t1.author_id = t2.author_id
;

select count(if(e>0 and c>0,1,null)) as num
from
(
select author_id,
sum(if(left(b.issue_id,1)='E',1,0)) as e,
sum(if(left(b.issue_id,1)='C',1,0)) as c
from issue_tb a
inner join answer_tb b
on a.issue_id = b.issue_id
group by author_id
) t

SELECT COUNT(DISTINCT author_id) num
FROM answer_tb AS a
LEFT JOIN issue_tb AS b
ON a.issue_id = b.issue_id
WHERE author_id IN ( SELECT DISTINCT author_id
                     FROM answer_tb AS a
                     LEFT JOIN issue_tb AS b
                     ON a.issue_id = b.issue_id
                     WHERE issue_type = 'Education')
AND issue_type = 'Career';

select
count(distinct b.author_id)
from issue_tb a
join answer_tb b
on a.issue_id=b.issue_id
where a.issue_type='Career' and b.author_id in 
(
    select
    distinct author_id
    from issue_tb a
    join answer_tb b
    on a.issue_id=b.issue_id
    where a.issue_type='Education'
)