sql查询学生大于平均值的成绩

情景

有一张表，里面是学生及其对应课程的成绩，要查出大于学生的所有课程平均值的课程。

表的结构

解决方法

我想到两种sql，如下

第一种

查出每个学生对应的 平均值再与原表连接，然后查询条件就比较该课程成绩分数和平均值

select t1.student_id,course_id
from t_mark t1,
	(
	SELECT student_id,AVG(mark) avg 
	from 
	t_mark
	group by student_id
	) t2
where t1.student_id = t2.student_id
and t1.mark > t2.avg

第二种

每次在查询条件使用子查询查出该学生的课程平均值比较

select t1.student_id,course_id
from t_mark t1
where t1.mark>
	(select AVG(mark)
	 from t_mark t2
	 where t2. student_id = t1.student_id)

比较两种sql

填充数据

为了有足够数据进行比较，写一个存储过程给3000个学生插入3条成绩数据

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_to_mark`()
BEGIN
	#Routine body goes here...
	DECLARE i INTEGER;
	SET i = 1;
	WHILE i<=3000 DO
		INSERT INTO t_mark 
		(student_id,course_id,mark)
		VALUES
		(i,1,RAND()*70+30),
		(i,2,RAND()*70+30),
		(i,3,RAND()*70+30);
		set i = i+1;
	END WHILE;
END

插入成功

查询比较

第一种

第二种

差距相当大。通过执行计划，看到第二个子查询是一个相关子查询（DEPENDENT SUBQUERY），第二个sql子查询中的参数是需要依赖外部查询，因此会对每个student_id 执行一次子查询，数据大时效率很低。

结论

还是使用第一种比较好，不知是否有更高效的查询sql欢迎评论 ~~