3
回答
一个关于MySQL多表查询的问题
华为云实践训练营,热门技术免费实践!>>>   

有4张表如下所示,怎么查询每个老师带领的学生工资平均值?

 

举报
austMin
发帖于2个月前 3回/159阅
select t.teach_no, t.teach_name, a1.avg_salary from teachers t
left join (
select a0.teach_no, avg(a0.salary) as avg_salary from (
select st.teach_no, s.stu_no, s.salary from stu_teach st 
left join salaries s on st.stu_no = t.stu_no 
) a0 group by a.teach_no
) a1 on t.teach_no = a1.teach_no;

没测试,sql大概是这样

--- 共有 1 条评论 ---
austMin不对呢!亲。。。。。 2个月前 回复
select t.teach_no,t.teach_name,avg(s.salary) from  salaries s left join teachers t  on t.teach_no = s.teach_no group by t.teach_no

 

--- 共有 1 条评论 ---
austMin不对呢。。。。。 2个月前 回复
SELECT E.TEACH_NAME, AVG(E.SALARY)
	FROM (SELECT SALARY, TEACH_NAME
					FROM SALARISE A, STUDENTS B, TEACHER C, STU_TEACH D
				 WHERE A.STU_NO = B.STU_NO
					 AND B.STU_NO = D.STU_NO
					 AND D.TEACH_NO = C.TEACH_NO) E
 GROUP BY E.TEACH_NAME

试一下这样吧,不使用join直接做多表联合查询。

顶部