有两个表
一个是program_enrolments,它存了选课的信息:学号(student)在students表中由id表示,年级(semester),
另一个是students,它存了学生的信息:学号(id),是否是国际学生(stype)。
我现在要算不同年级,国际学生占全部学生的比例。
Create or replace view Q7(sem,num)
as
select count(student),Q6(semester)
from program_enrolments
where student in
( select id
from students
where stype='intl'
)
group by Q6(semester)
order by Q6(semester)
这样求出了国际学生的数量,
但是怎么求所占比例?
有办法分别别用student1,student2表示国际学生和全部学生吗?
理想情况是这样:
Create or replace view Q7(sem,num)
as
select count(studen_1)/count(student_2),Q6(semester)
from program_enrolments
where student_1 in
( select id
from students
where stype='intl'
)
and student_1 in
( select id
from students)
group by Q6(semester)
order by Q6(semester)
如果你的两张表是一对一的关系,那么下面的SQL应该可以满足你的要求吧。
假设stype为 'I' 表示国际生
SELECT
SUM(IF(s.stype = 'I', 1, 0)) as number_international,
SUM(1) AS number_total,
SUM(IF(s.stype = 'I', 1, 0)) / SUM(1) AS rate_international
FROM program_enrolments pe
INNER JOIN students s on s.student_id = pe.student_id
GROUP BY pe.semester;