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表示回答字数）：

示例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(t1.issue_id) AS num
FROM answer_tb t1 JOIN issue_tb t2 ON t1.issue_id = t2.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'

select count(t.author_id) num
from( select b.author_id,count(distinct a.issue_type) num
      from issue_tb a join answer_tb b using(issue_id)
      where a.issue_type="Education" or a.issue_type="Career" 
      group by b.author_id
      having num>1
     ) t

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

SELECT COUNT(1) FROM answer_tb 
WHERE  issue_id in ('C001','C002','C003')
AND author_id IN (
SELECT author_id FROM issue_tb A 
LEFT JOIN answer_tb B 
ON A.issue_id=B.issue_id
where B.issue_id in ('E001','E002','E003'))

select count(distinct a1.author_id) as num 
from answer_tb a1 
join answer_tb a2 on a1.author_id=a2.author_id
where a1.issue_id in('E001','E002','E003')
and a2.issue_id in('C001','C002','C003','C004')


# 以e开头：