统计不同国籍教师所开课程的学生总数,对于没有任课的教师,学生总人数计为 0 。最后查询教师国籍是 'U' 开头且学生总数在 2000 到 5000 之间的教师国籍及学生总数 (别名为 student_count ),结果按照学生总数降序排列,如果学生总数相同,则按照教师国籍升序排列。
表定义 1:courses(课程表)
列名 类型 注释
id int unsigned 主键
name varchar 课程名称
student_count int 学生总数
created_at date 创建时间
teacher_id int 讲师 id
表定义 2:teachers(教师表)
列名 类型 注释
id int unsigned 主键
name varchar 教师姓名
email varchar 教师邮箱
age int 教师年龄
country varchar 教师国籍
select country,sum(student_count)as student_count
from (select country ,student_count
from courses c
left join teachers t
on c.teacher_id=t.id
where t.country like 'U%') as q
group by country
having sum(student_count) between 2000 and 5000 ;
select country,student_count from teachers a left join courses b on a.id = b.teacher_id
where a.country like 'U%' and b.student_count between 2000 and 5000
order by a.student_count desc,a.country desc