Mysql如何连接2个子查询

Question

我有这个 mysql 代码:

select 
    course, students
from
    (select 
         course_id, count(*) as students
     from 
         taughtby
     where 
         u in (select s_id from student where s_id = u)
     group by 
         course_id) as B;

select 
    course, professors
from
    (select 
         course_id, u_id, count(*) as professors
     from 
         taughtby
     where 
         u in (select p_id from professor where p_id = u)
     group by 
         course_id) as A

我的代码在 mysql 工作台中返回 2 个不同的结果集。

有什么方法可以连接这两个结果集吗？

Answer 1

您可以在三个表之间使用双join子句:

 select course_id, count(distinct p_id) as professors, count(distinct s_id) as students
   from taughtby
   left join professor on p_id = u_id
   left join student on s_id = u_id
  group by course_id

包括不同关键字。