sql面试题（二）多表连接分组groupby 和聚合函数

---- 表一学生表
CREATE TABLE `student` (
  `sid` int NOT NULL AUTO_INCREMENT,
  `stu_name` varchar(100) NOT NULL,
  `sex` varchar(40) NOT NULL,
  `score` varchar(40) NOT NULL,
  `birth_date` date DEFAULT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--表二班级表
-- baifen.class definition

CREATE TABLE `class` (
  `cid` int NOT NULL,
  `cname` varchar(40) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 表三学生班级关系表
-- baifen.stu_class definition

CREATE TABLE `stu_class` (
  `class_id` int NOT NULL,
  `stu_id` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 问题，求出每个班级女生人数以及女生的平均分？

-- 方法一

select

c.cid,s.sex,AVG(score) 

from

student s

left join stu_class sc on

s.sid = sc.stu_id

left join class c on

c.cid = sc.class_id

group by c.cid,sex HAVING sex ='女'

 

-- 方法二

select

c.cid,s.sex,AVG(score) 

from

student s

left join stu_class sc on

s.sid = sc.stu_id

left join class c on

c.cid = sc.class_id

where s.sex='女'

group by c.cid,sex 

-- 答题解析：

谁多谁做基准表，关联关系是班级id和学生id,然后基于性别分组聚合筛选，或者先筛选再聚合