【难度】:简单 【分类】:子查询
分析思路
难点:
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'