首页 > 试题广场 >

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

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

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

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

Mysql

面试中的华夫饼很有胆量

SELECT COUNT(DISTINCT a.author_id) AS num
FROM answer_tb a
INNER JOIN issue_tb b ON a.issue_id = b.issue_id
WHERE b.issue_type IN ('Education', 'Career')
GROUP BY a.author_id
HAVING COUNT(DISTINCT b.issue_type) = 2;

给出一种简洁的办法，不需要子查询。但好像只能通过自测输入，不能通过提交，希望有大神帮忙看一下

发表于 2025-03-23 12:48:09 回复(0)

Mysql

西米露离不开奶茶

with cte 
as(
    select author_id, issue_type
    from answer_tb a 
        left join issue_tb i
        on a.issue_id = i.issue_id
)
select count(distinct author_id) as num
from cte
where author_id in (
                    select distinct author_id 
                    from cte
                    where issue_type = 'Education'
                    )
    and issue_type = 'career'

发表于 2025-03-15 19:32:49 回复(0)

Mysql

每天都很饿

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

发表于 2024-07-11 17:48:29 回复(0)

Mysql

多巴胺的囚徒

ok了，来一个最简单贴题目的思路,用户在完成了教育类的问题后，又去回答了职场问题，

即user_id 在回答了教育问题的user_id里面

然后现在的问题类型是职场，代码如下

select count(*)
from answer_tb t1
left join issue_tb t2
using(issue_id)
where author_id in (select author_id from answer_tb t1 left join issue_tb t2 on t1.issue_id = t2.issue_id   where issue_type = 'Education') 
and issue_type = 'Career'

发表于 2024-06-06 22:54:20 回复(0)

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

发表于 2024-04-29 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)