mysql子查询结果合并

有三个表分别是班级，选课，学生，表结构如下

CREATE TABLE `班级` (
  `班级名称` varchar(10) NOT NULL,
  `所属学院` varchar(10) NOT NULL,
  `辅导员` varchar(8) DEFAULT NULL,
  `自习室` varchar(12) DEFAULT NULL,
  PRIMARY KEY (`班级名称`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

CREATE TABLE `选课` (
  `学号` char(6) NOT NULL DEFAULT '',
  `课程号` char(3) NOT NULL DEFAULT '',
  `成绩` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`学号`,`课程号`),
  KEY `选课_fk2` (`课程号`),
  CONSTRAINT `选课_fk2` FOREIGN KEY (`课程号`) REFERENCES `课程` (`课程号`),
  CONSTRAINT `选课_ibfk_1` FOREIGN KEY (`学号`) REFERENCES `学生` (`学号`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

CREATE TABLE `学生` (
  `学号` char(6) NOT NULL,
  `姓名` varchar(8) NOT NULL,
  `性别` char(1) DEFAULT NULL,
  `年龄` tinyint(4) DEFAULT '20',
  `所在班级` varchar(10) DEFAULT NULL,
  `籍贯` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`学号`),
  KEY `所在班级` (`所在班级`),
  CONSTRAINT `学生_ibfk_1` FOREIGN KEY (`所在班级`) REFERENCES `班级` (`班级名称`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

练习（1）

SELECT
`学生`.`学号`,
`学生`.`姓名`,
`班级`.`班级名称`,
`班级`.`所属学院`,
`班级`.`辅导员`
FROM
`学生`
INNER JOIN `班级` ON `学生`.`所在班级` = `班级`.`班级名称`
where   `学生`.`学号` not in(SELECT `学号` from `选课`);

练习（2）

SELECT
`学生`.`学号`,
`学生`.`姓名`,
`班级`.`班级名称`,
`班级`.`所属学院`,
`班级`.`辅导员`,
COUNT(`选课`.`学号`) as 选课数量
FROM
`学生`
INNER JOIN `班级` ON `学生`.`所在班级` = `班级`.`班级名称`
left JOIN `选课`  on  `选课`.`学号`=`学生`.`学号`
--  WHERE not ISNULL(`选课`.`成绩` )
GROUP BY `学号` ;

练习（3）

– 显示自习室学习的学生信息以及学生选课数量（所属学院，自习室，自习人数，自习科目数量）自习科目数量是所有自习学生选课科目人总和

方法1，直接用技术函数

SELECT
bj.`自习室` ,
COUNT(DISTINCT xs.`学号`) as  自习人数,
COUNT( xk.`课程号`) as  科目人数
FROM
`班级` bj
LEFT JOIN  `学生` xs ON  bj.`班级名称`=xs.`所在班级`
LEFT JOIN  `选课` xk on  xs.`学号` =xk.`学号`
GROUP BY bj.`自习室`

方法2

自习人数

SELECT   bj.`所属学院`,bj.`自习室`,COUNT(xs.`姓名`) as 自习人数
from `班级` bj  
left JOIN `学生` xs on bj.`班级名称`=xs.`所在班级`
GROUP BY bj.`自习室`,bj.`所属学院`;

自习科目数

SELECT   bj.`所属学院`,bj.`自习室`,COUNT(*) as 自习科目数
from `班级` bj  
left JOIN `学生` xs on bj.`班级名称`=xs.`所在班级`
LEFT JOIN `选课` xk on xs.`学号`=xk.`学号`
GROUP BY bj.`自习室`,bj.`所属学院`;

最后合并结果的查询SQL

SELECT t1.*,COALESCE(t2.自习科目数, 0) as 自习科目数
from 
(
SELECT   bj.`所属学院`,bj.`自习室`,COUNT(xs.`姓名`) as 自习人数
from `班级` bj  
left JOIN `学生` xs on bj.`班级名称`=xs.`所在班级`
GROUP BY bj.`自习室`,bj.`所属学院`) as t1 left JOIN
(
SELECT   bj.`所属学院`,bj.`自习室`,COUNT(*) as 自习科目数
from `班级` bj  
JOIN `学生` xs on bj.`班级名称`=xs.`所在班级`
JOIN `选课` xk on xs.`学号`=xk.`学号`
GROUP BY bj.`自习室`,bj.`所属学院`) as t2
on t1.`自习室`=t2.`自习室`;

mysql 8.0以上版本
用with将结果前置

WITH t1 as
(
SELECT   bj.`所属学院`,bj.`自习室`,COUNT(xs.`姓名`) as 自习人数
from `班级` bj  
left JOIN `学生` xs on bj.`班级名称`=xs.`所在班级`
GROUP BY bj.`自习室`,bj.`所属学院`)  ,
t2 as
(
SELECT   bj.`所属学院`,bj.`自习室`,COUNT(*) as 自习科目数
from `班级` bj  
left JOIN `学生` xs on bj.`班级名称`=xs.`所在班级`
LEFT JOIN `选课` xk on xs.`学号`=xk.`学号`
GROUP BY bj.`自习室`,bj.`所属学院`) 

SELECT 
    t1.`所属学院`,
    t1.`自习室`,
    t1.自习人数,
    t2.自习科目数
FROM
    t1
        JOIN
    t2 ON t1.`所属学院` = t2.`所属学院` AND t1.`自习室` = t2.`自习室`;