MySQL(3) 数据库查询练习题

MySQL(3) 数据库查询练习题

#4、查询参加[平均成绩最低科目]考试的学员成绩前三名的学员所在的地址信息
select
	J.subjectId,J.subjectName,S.stuId,stuName,provinceName ,cityName ,countyName
from 
	subject J
inner join
	score R
on
	J.subjectId=R.subjectId

inner join
	student S
on
	S.stuId=R.stuId
inner join
	Province P
on
	S.provinceId=P.provinceId
inner join
	city C
on
	S.cityId=C.cityId
inner join
	county Y
on
	S.countyId=Y.countyId
where
	J.subjectId
in
(
	select
		subjectId
	from
	(
		select
			subjectId,avg(score) avgScore
		from
			score S
		group by
			subjectId
		order by
			avgScore ASC
		limit
			0,1
	)s
)
group by
	S.stuId
order by
	avg(score) DESC
limit
	0,3;