首页 > 试题广场 >

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

[编程题]某乎问答回答过教育类问题的用户里有多少用户回答过职场类问题
  • 热度指数：26961 时间限制：C/C++ 1秒，其他语言2秒 空间限制：C/C++ 256M，其他语言512M
  • 算法知识视频讲解
现有某乎问答题目信息表issue_tb如下（其中issue_id代表问题编号，issue_type表示问题类型）：
issue_id issue_type
E001 Education
E002 Education
E003 Education
C001 Career
C002 Career
C003 Career
C004 Career
P001 Psychology
P002 Psychology
创作者回答情况表answer_tb如下（其中answer_date表示创作日期、author_id指创作者编号、issue_id指回答问题编号、char_len表示回答字数）：
answer_date author_id issue_id char_len
2021-11-01 101 E001 150
2021-11-01
 101 E002 200
2021-11-01
 102 C003 50
2021-11-01
 103 P001 35
2021-11-01
 104 C003 120
2021-11-01
 105 P001 125
2021-11-01
 102 P002 105
2021-11-02
 101 P001 201
2021-11-02
 110 C002 200
2021-11-02
 110 C001 225
2021-11-02
 110 C002 220
2021-11-03
 101 C002 180
2021-11-04
 109 E003 130
2021-11-04
 109 E001 123
2021-11-05
 108 C001 160
2021-11-05
 108 C002 120
2021-11-05
 110 P001 180
2021-11-05
 106 P002 45
2021-11-05
 107 E003 56
请你统计回答过教育类问题的用户里有多少用户回答过职场类问题，以上例子的输出结果如下：
num
1

示例1

输入

drop table if exists issue_tb;
CREATE TABLE issue_tb(
issue_id char(10) NOT NULL, 
issue_type char(10) NOT NULL);
INSERT INTO issue_tb VALUES('E001' ,'Education');
INSERT INTO issue_tb VALUES('E002' ,'Education');
INSERT INTO issue_tb VALUES('E003' ,'Education');
INSERT INTO issue_tb VALUES('C001', 'Career');
INSERT INTO issue_tb VALUES('C002', 'Career');
INSERT INTO issue_tb VALUES('C003', 'Career');
INSERT INTO issue_tb VALUES('C004', 'Career');
INSERT INTO issue_tb VALUES('P001' ,'Psychology');
INSERT INTO issue_tb VALUES('P002' ,'Psychology');

drop table if exists answer_tb;
CREATE TABLE answer_tb(
answer_date date NOT NULL, 
author_id int(10) NOT NULL,
issue_id char(10) NOT NULL,
char_len int(10) NOT NULL);
INSERT INTO answer_tb VALUES('2021-11-1', 101, 'E001' ,150);
INSERT INTO answer_tb VALUES('2021-11-1', 101, 'E002', 200);
INSERT INTO answer_tb VALUES('2021-11-1',102, 'C003' ,50);
INSERT INTO answer_tb VALUES('2021-11-1' ,103, 'P001', 35);
INSERT INTO answer_tb VALUES('2021-11-1', 104, 'C003', 120);
INSERT INTO answer_tb VALUES('2021-11-1' ,105, 'P001', 125);
INSERT INTO answer_tb VALUES('2021-11-1' , 102, 'P002', 105);
INSERT INTO answer_tb VALUES('2021-11-2',  101, 'P001' ,201);
INSERT INTO answer_tb VALUES('2021-11-2',  110, 'C002', 200);
INSERT INTO answer_tb VALUES('2021-11-2',  110, 'C001', 225);
INSERT INTO answer_tb VALUES('2021-11-2' , 110, 'C002', 220);
INSERT INTO answer_tb VALUES('2021-11-3', 101, 'C002', 180);
INSERT INTO answer_tb VALUES('2021-11-4' ,109, 'E003', 130);
INSERT INTO answer_tb VALUES('2021-11-4', 109, 'E001',123);
INSERT INTO answer_tb VALUES('2021-11-5', 108, 'C001',160);
INSERT INTO answer_tb VALUES('2021-11-5', 108, 'C002', 120);
INSERT INTO answer_tb VALUES('2021-11-5', 110, 'P001', 180);
INSERT INTO answer_tb VALUES('2021-11-5' , 106, 'P002' , 45);
INSERT INTO answer_tb VALUES('2021-11-5' , 107, 'E003', 56);

输出

1
算法知识视频讲解
添加回答
Mysql
牛客11111413号头像 牛客11111413号
理解为有几个用户既回答了教育类又回答了职场类，但是要注意自连接 
select COUNT(DISTINCT author_id)
#     author_id,at1.issue_id,at2.issue_id
from answer_tb as at1
    join answer_tb as at2 using(author_id)
where at1.issue_id like 'E%' and at2.issue_id like 'C%'
发表于 2022-02-13 15:38:55 回复(2)
Mysql
BLAcKSQAoO头像 BLAcKSQAoO
鉴于本题数据依旧简单，可以直接从author_id的首字母推出这个问题分属什么类别，那么issue_tb就一边凉快去吧。。。 

SELECT 
  COUNT(DISTINCT author_id) num
FROM answer_tb
WHERE issue_id LIKE 'E%'
      AND
      author_id IN (SELECT 
                      author_id
                    FROM answer_tb
                    WHERE issue_id LIKE 'C%')


发表于 2021-12-07 09:42:56 回复(1)
Mysql
酸菜鱼土豆大侠头像 酸菜鱼土豆大侠

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

分析思路

难点：

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'
发表于 2022-11-01 21:13:19 回复(0)
Mysql
蒋点数分头像 蒋点数分 
-- 该问题就是 求同时回答过教育类和职场类的用户数

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
;

发表于 2021-12-17 21:34:57 回复(1)
Mysql
War张头像 War张 
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

发表于 2023-08-05 21:31:52 回复(0)
Mysql
Yusuf!!!头像 Yusuf!!!
select count(t1.author_id)
from(
    select distinct(author_id)
    from answer_tb a
    join issue_tb i 
    on a.issue_id = i.issue_id
    where issue_type = 'Career')t1
join
    (select distinct(author_id)
    from answer_tb a
    join issue_tb i 
    on a.issue_id = i.issue_id
    where issue_type = 'Education')t2
on t1.author_id = t2.author_id
发表于 2021-12-15 07:15:53 回复(0)
爱刷美剧的柯基在许愿头像 爱刷美剧的柯基在许愿 
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';

发表于 2024-08-04 20:01:23 回复(0)
Marcus_J头像 Marcus_J 
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)

发表于 2024-05-19 12:20:29 回复(0)
4fbd81e头像 4fbd81e 
-- 庆幸的是：不是求回答过教育类问题的用户里有多少比例的用户回答过职场类问题
-- 导致这一题一下子变为简单题

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

发表于 2023-01-08 18:32:17 回复(0)
Mysql
zyt33头像 zyt33
select count(distinct author_id)
from answer_tb left join issue_tb using(issue_id)
where author_id in (select distinct author_id from 
                    answer_tb left join issue_tb using(issue_id)
                   where issue_type = "Education" )
and issue_type = "Career"
发表于 2022-05-21 01:29:39 回复(0)
Hudas.头像 Hudas.
SELECT COUNT(DISTINCT author_id) AS num
FROM answer_tb
WHERE issue_id LIKE 'E%'
AND author_id IN(SELECT author_id 
                 FROM answer_tb
                 WHERE issue_id LIKE 'C%');
发表于 2021-12-27 11:02:11 回复(0)
浅浅的夏天_头像 浅浅的夏天_ 
# 一、筛选出回答过教育类的用户id
#二、根据存在一中的用户，再次刷选回答过职场类用户
#三、 计数

select count(distinct author_id) num
from
(
    select 
    author_id

    from answer_tb a
    join issue_tb b
    on a.issue_id=b.issue_id

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

发表于 2025-06-28 13:35:48 回复(0)
kirua丶头像 kirua丶
select
count(author_id)
from
(
    select distinct
        author_id
        from
            (
            select
            author_id
            ,issue_id
            from answer_tb
            left join issue_tb
            using(issue_id)
            where issue_type in ('Career')
            )a

    where author_id in
        (
        select distinct
        author_id
        from
            (
            select
            author_id
            ,issue_id
            from answer_tb
            left join issue_tb
            using(issue_id)
            where issue_type in ('Education')
            )a
        )
)a
发表于 2025-05-20 20:35:37 回复(0)
kds1头像 kds1
一种弱智解法，不要学。 
select count(author_id) as num
from (
select author_id,count(distinct issue_type) as num
from answer_tb at left join issue_tb it on at.issue_id=it.issue_id
where issue_type <>"Psychology"
group by author_id
having num>=2) a 

发表于 2025-05-17 14:41:53 回复(0)
聪明的回笼觉觉主最喜欢春天头像 聪明的回笼觉觉主最喜欢春天
select count(distinct author_id)
from answer_tb
left 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'

发表于 2025-05-09 16:12:18 回复(0)
牛客997640975号头像 牛客997640975号 
select
    count(distinct author_id) as num
from answer_tb
join issue_tb
using(issue_id)
where author_id in
(
select
    distinct author_id
from answer_tb
join issue_tb
using(issue_id)
where issue_type='Education'
) and issue_type='Career'

发表于 2025-04-17 10:28:58 回复(0)
牛客409135028号头像 牛客409135028号 
with t as(
select *
from answer_tb
left join issue_tb
using(issue_id)
)
select count(distinct author_id) as num
from(
    select distinct author_id
    from t
    where issue_type='Education'
) as t1
join t
using(author_id)
where issue_type='Career'

发表于 2025-04-13 03:55:27 回复(0)
Mysql
等offer的萝卜头像 等offer的萝卜
SELECT COUNT(DISTINCT author_id) AS num
FROM answer_tb at JOIN issue_tb it USING(issue_id)
WHERE issue_type = 'Career' AND author_id IN(
    SELECT DISTINCT author_id
    FROM answer_tb at JOIN issue_tb it USING(issue_id)
    WHERE issue_type = 'Education'
);
发表于 2025-04-10 10:04:38 回复(0)
Lryyy头像 Lryyy
with tb AS(
    SELECT issue_tb.issue_id, issue_tb.issue_type, answer_tb.author_id FROM issue_tb INNER JOIN answer_tb
    ON issue_tb.issue_id = answer_tb.issue_id
)
SELECT COUNT(distinct a.author_id)
FROM tb a
INNER JOIN tb b
ON a.author_id = b.author_id
AND a.issue_type = 'Education'
AND b.issue_type = 'Career'
发表于 2025-04-09 06:31:34 回复(0)
qscgyjil头像 qscgyjil
with t11 as
(
select  author_id,issue_type
from
answer_tb as t1
left join issue_tb as t2
on t1.issue_id = t2.issue_id
),
t12 as
(
select distinct author_id
from t11 where issue_type = 'Education'),
t13 as
(
select distinct author_id
from t11
where issue_type = 'Career') ,
t14 as
(
select author_id from t12 where author_id in (select author_id from t13))
select count(*) from t14

发表于 2025-04-02 17:04:01 回复(0)
提交观点

问题信息

难度：
161条回答 1124浏览

热门推荐

通过挑战的用户

查看代码

相关试题

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

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号