首页 > 试题广场 >

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

[编程题]某乎问答回答过教育类问题的用户里有多少用户回答过职场类问题
  • 热度指数:19688 时间限制: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
理解为有几个用户既回答了教育类又回答了职场类,但是要注意自连接
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)
鉴于本题数据依旧简单,可以直接从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)
-- 该问题就是 求同时回答过教育类和职场类的用户数

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)

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

分析思路

难点:

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)
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)
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)
-- 庆幸的是:不是求回答过教育类问题的用户里有多少比例的用户回答过职场类问题
-- 导致这一题一下子变为简单题

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)
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)
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)
select count(*)
from
(select distinct author_id
from answer_tb a left join issue_tb b on a.issue_id = b.issue_id
group by author_id
having sum(if(issue_type = 'Education',1,0)) >= 1 and
sum(if(issue_type = 'Career',1,0)) >= 1) t1
发表于 2024-03-24 13:43:44 回复(0)
这些真是真题吗?
发表于 2024-03-22 09:19:13 回复(0)
select count(author_id) num
from(
select distinct author_id from answer_tb join issue_tb using( issue_id)
where issue_type='Career')t
join(
select distinct author_id from answer_tb join issue_tb using( issue_id)
where issue_type='Education')t1
using(author_id)

发表于 2024-02-23 11:31:57 回复(0)
SELECT
    count(distinct author_id) as num
from
    answer_tb as s1
left join
    issue_tb as s2
on
    s1.issue_id = s2.issue_id
where
    author_id in
        (SELECT
            author_id
        from
            (SELECT
                author_id,
                t1.issue_id,
                issue_type
            from
                answer_tb as t1
            left join
                issue_tb as t2
            on  
                t1.issue_id = t2.issue_id) s3
        where
        issue_type = 'Education') 
and
    issue_type = 'Career';

编辑于 2024-02-05 10:10:11 回复(0)
select count( distinct author_id) num 
from(
select 
    author_id
from answer_tb join issue_tb
using(issue_id)
where issue_type = 'Education'
    )t1
join
(select 
    author_id
from answer_tb join issue_tb
using(issue_id)
where issue_type = 'Career'
    )t2
using(author_id)

发表于 2023-11-30 12:25:17 回复(0)
with 
    t1 as(
        select author_id
        from answer_tb
        where issue_id regexp '^E'
    )
    ,t2 as(
        select author_id
        from answer_tb
        where issue_id regexp '^C'
    )
select count(distinct t1.author_id) num
from t1
join t2
on t1.author_id = t2.author_id

发表于 2023-11-28 11:49:25 回复(0)
select count(distinct a.aid) as num
from
(select distinct author_id aid
from answer_tb
join issue_tb using(issue_id)
where issue_type='Education') a
join
(select distinct author_id bid
from answer_tb
join issue_tb using(issue_id)
where issue_type='Career') b
on a.aid=b.bid;

发表于 2023-11-18 22:55:22 回复(0)
SELECT
count(DISTINCT ast.author_id) num
from answer_tb as ast left join issue_tb ist on ast.issue_id = ist.issue_id
WHERE ast.author_id in 
(SELECT
DISTINCT ast.author_id
from answer_tb as ast left join issue_tb ist on ast.issue_id = ist.issue_id WHERE ist.issue_type = 'Education')
and ist.issue_type = 'Career'

发表于 2023-10-20 16:13:07 回复(0)
select count(*) as num from ( 
select author_id
from answer_tb left join issue_tb using(issue_id)
where issue_type in ('Education','Career')
group by author_id
having count(distinct issue_type)>=2  ) a

发表于 2023-10-10 11:05:10 回复(0)
select distinct
    sum(if (count(distinct issue_type) = 2, 1, 0)) over ()
from
    answer_tb a
    inner join issue_tb i on i.issue_id = a.issue_id
    and issue_type in ('Education', 'Career')
group by
    author_id

发表于 2023-10-05 23:35:42 回复(0)
select
    count(distinct a1.author_id)
from
    answer_tb as a1
    join answer_tb as a2 on a1.author_id = a2.author_id
where
    a1.issue_id in (
        select
            issue_id
        from
            issue_tb
        where
            issue_type = 'Education'
    )
    and a2.issue_id in (
        select
            issue_id
        from
            issue_tb
        where
            issue_type = 'Career'
    )

发表于 2023-09-12 10:56:05 回复(0)
  • 二维码

    扫描二维码,关注牛客网

  • 二维码

    下载牛客APP,随时随地刷题