SELECT e.stu_no as stuNo ,e.estimation_id as estimationId,
(select IFNULL(weight,0) from evaluation_type where evalution_method_id=1 and estimation_id=e.estimation_id) as zp,
(select IFNULL(weight,0) from evaluation_type where evalution_method_id=2 and estimation_id=e.estimation_id) as hp,
(select IFNULL(weight,0) from evaluation_type where evalution_method_id=3 and estimation_id=e.estimation_id) as bzrpf,
(select evaluation_value from tb_evaluation where evaluation_method=1 and stu_no=e.stu_no and estimation_id=e.estimation_id) as selfAssessment
,(select AVG(evaluation_value) from tb_evaluation where evaluation_method=2 and stu_no=e.stu_no and estimation_id=e.estimation_id) as avgMutual,
(select AVG(evaluation_value) from tb_evaluation where evaluation_method=3 and stu_no=e.stu_no and estimation_id=e.estimation_id) as avgTeacherAssessment,gs.name as stuName,gg.name as gradeName,gc.name as className FROM `tb_evaluation` e LEFT JOIN general_student gs on e.stu_no=gs.student_no
LEFT JOIN general_grade gg on gs.grade_id=gg.id LEFT JOIN general_class gc on gs.class_id=gc.id GROUP BY e.stu_no , e.estimation_id
mysql多表整合查询例子
最新推荐文章于 2023-06-09 00:00:14 发布