首页 > 试题广场 >

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

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

热度指数：20347 时间限制：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

输入

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);

输出

算法知识视频讲解

Mysql

yuciaaa

 WITH A AS(
    SELECT DISTINCT author_id, issue_type
    FROM answer_tb
    LEFT JOIN issue_tb ON 
    issue_tb.issue_id = answer_tb.issue_id
 ), B AS(
    SELECT author_id FROM A
    WHERE issue_type = 'Education'
 )
 SELECT COUNT(*) FROM(
    SELECT author_id FROM A 
    WHERE issue_type = 'Career'
    AND author_id IN (SELECT * FROM B)
 ) AS TT

发表于今天 13:30:14 回复(0)

Mysql

祖拜尔实习生

select
    count(distinct author_id) num
from
    answer_tb ans
    left join issue_tb iss on ans.issue_id = iss.issue_id
where
    author_id in (
        select
            author_id
        from
            answer_tb ans
            left join issue_tb iss on ans.issue_id = iss.issue_id
        where
            issue_type = 'Education'
    )
    and issue_type = 'Career'

编辑于 2024-04-21 10:04:13 回复(0)

Mysql

牛客635241710号

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)

Mysql

牛客744468654号

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)

Mysql

swbl_ww

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)

Mysql

dolen

-- 请你统计回答过教育类问题的用户里有多少用户回答过职场类问题，以上例子的输出结果如下：
-- 注意：此类问题没有时间先后顺序

select 
# t2.answer_date,t3.answer_date,t2.issue_id,t3.issue_id 
count(distinct t2.author_id ) num
from answer_tb t2 
  inner join answer_tb t3 on t2.author_id = t3.author_id 
  where left(t2.issue_id,1)='E' 
   and left(t3.issue_id,1)='C'

发表于 2023-08-11 17:39:42 回复(0)

Mysql

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

JIE19

select count(distinct author_id) as num
from answer_tb
where issue_id in (select issue_id from issue_tb where issue_type='Education')
and author_id in (SELECT author_id FROM answer_tb where issue_id in(select issue_id from issue_tb where issue_type='career'))

发表于 2023-08-05 16:34:52 回复(0)

Mysql

长白山啊

SELECT
    count(t1.author_id) as num
FROM
    (
        (
            SELECT DISTINCT
                author_id
            FROM
                answer_tb
            WHERE
                issue_id IN (
                    SELECT
                        issue_id
                    FROM
                        issue_tb
                    WHERE
                        issue_type = 'Education'
                )
        ) AS t1
        INNER JOIN (
            SELECT DISTINCT
                author_id
            FROM
                answer_tb
            WHERE
                issue_id IN (
                    SELECT
                        issue_id
                    FROM
                        issue_tb
                    WHERE
                        issue_type = 'Career'
                )
        ) AS t2 ON t1.author_id = t2.author_id
    )

发表于 2023-05-05 14:25:39 回复(0)

Mysql

isabener

select count(distinct author_id) as num
from answer_tb a
join issue_tb i
on a.issue_id = i.issue_id
where a.author_id in (
    select a.author_id
    from answer_tb a
    join issue_tb i
    on a.issue_id = i.issue_id
    where i.issue_type = "Education"
) and i.issue_type = "Career"

发表于 2023-04-25 21:14:13 回复(0)

Mysql

我叫谢小盟，叫我查尔斯好了

select count(distinct author_id)

from 

answer_tb as a left join issue_tb as i using(issue_id)

where author_id in 

(select author_id
from answer_tb as a left join issue_tb as i using(issue_id)
where issue_type = "Education"
) 

and issue_type = "Career"

发表于 2023-04-19 15:35:35 回复(0)

Mysql

Kalina2023

挺简单的。子查询和自连接都行

这里用的子查询

-- 聚合所有信息
with total_info as
(
select
    at.author_id,
    it.issue_type
from answer_tb at
left join issue_tb it 
on it.issue_id = at.issue_id
)

select count(distinct author_id) as num
from total_info
where issue_type = 'Career'
and author_id in
    (
    select distinct author_id
    from total_info
    where issue_type = 'Education' 
    )

发表于 2023-03-29 17:20:04 回复(0)

Mysql

牛客129587388号

with t as (select a.*,b.issue_type from answer_tb a left join issue_tb b on a.issue_id=b.issue_id )

select
    count(distinct author_id) as num
from 
    answer_tb
where 
    (author_id in (select author_id from t where issue_type='Education')) 
    and 
    (author_id in (select author_id from t where issue_type='Career'))

发表于 2023-03-28 22:24:05 回复(0)

Mysql

小宏的大牛之路

select count(distinct author_id)
from (
    select author_id
    from answer_tb
        where issue_id in (
        select issue_id
        from issue_tb
        where issue_type='Education'
    )
)t
where author_id in (
    select author_id
    from answer_tb
    where issue_id in (
        select issue_id
        from issue_tb
        where issue_type='Career'
    )
)

发表于 2023-01-19 16:32:20 回复(0)

Mysql

牛客314814074号

with s1 as (select

author_id,issue_type

from issue_tb as t1 join answer_tb as t2

on t1.issue_id = t2.issue_id

)

select count(1) from

(

select

t3.author_id

from s1 as t3 join s1 as t4 on t3.author_id = t4.author_id

where (t3.issue_type = 'Education' and t4.issue_type = 'Career')

or (t4.issue_type = 'Education' and t3.issue_type = 'Career')

group by t3.author_id) as s2

发表于 2022-12-13 16:16:19 回复(0)

Mysql

SQLboy__zk

--理解为:一个作者下面既有教育类也有职场类的题被回答就满足条件
with t1 as(
select distinct author_id,b.issue_type from answer_tb a
    left join issue_tb b on a.issue_id = b.issue_id 
    where b.issue_type = 'Education'&nbs***bsp;
     b.issue_type = 'Career' 
)select count(1) num 
from (
    select count(1) cnt from t1 group by t1.author_id having count(1) = 2
)t

发表于 2022-11-15 17:05:18 回复(0)

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(distinct author_id) as num
from answer_tb where issue_id in (
    select issue_id from issue_tb 
    where issue_type = 'Education' and author_id in( 
        select distinct author_id from answer_tb #查询回答过教育类问题的author_id
            where issue_id in (            
            select issue_id from issue_tb  #查询教育类问题的issue_id
            where issue_type = 'Career'
        )
    )
)
利用三个子查询

发表于 2022-10-27 15:34:54 回复(0)

Mysql

曼彻斯特.

select count(*) num
from (
         select author_id, sum(a1) l1, sum(a2) l2
         from (
                  select author_id,
                         issue_type,
                         if(issue_type = 'Education', 1, null) as a1,
                         if(issue_type = 'Career', 1, null)    as a2
                  from answer_tb t1
                           inner join issue_tb t2 on t1.issue_id = t2.issue_id)
                  as t1
         group by author_id) t2
where l1 >= 1
  and l2 >= 1
;

发表于 2022-10-07 21:41:58 回复(0)