{

“Datevalue”:” 返回要统计的月”,

“Subject”:

[

{

“Subject”:”科目”,

“Avescore”: {“1”:”70|65”,”2”:90|89”,……,”31”:”100|90” } , // 个人平均|其它学员平均 “”:” 复习状况”, 同上

“”:” 专注程度” , 同上

“”:” 理解状况”, 同上

“”:” 运用能力”, 同上

“”:” 课堂态度”, 同上

},

……

]

}

-- 用3个临时表

-- 1:获得这个月，自己有几个科目 获取到  学生id,科目id,科目名称

SELECT c.`SubjectId`,c.`StudentId` ,s.`SubjectName`

FROM `tb_fdt_courseitem` c INNER JOIN `tb_fdt_performance` p INNER JOIN `tb_ci_subject` s

ON c.`CourseItemId`=p.`CourseItemId`  AND c.`SubjectId`=s.`SubjectId`

WHERE c.`AgentId`='07551001'

AND c.`StudentId`='0e02d67f-02ba-4cb7-87de-d5e1d4dcfa8e'

AND c.`StartDate`>='2013-01' AND c.`EndDate`<='2013-02'

GROUP BY subjectid

 

-- 2:根据科目的Id，来知道自己这个月，哪几天是有课的，然后根据课程ID来获取自己的平均值

SELECT c.`StudentId` ,c.`SubjectId` ,AVG(Avescore) AS j_Avescore,AVG(Reviewscore) AS j_Reviewscore,AVG(Focusscore) AS j_Focusscore,AVG(Understandscore) AS j_Understandscore,

AVG(Applyscore) AS j_Applyscore,AVG(Mannerscore) AS j_Mannerscore,DATE_FORMAT(c.`StartDate`,'%Y-%m-%d') AS everyday

FROM `tb_fdt_courseitem` c INNER JOIN `tb_fdt_performance` p

ON c.`CourseItemId`=p.`CourseItemId`

WHERE c.`AgentId`='07551001'

AND c.`SubjectId`='1'

AND c.`StudentId`='0e02d67f-02ba-4cb7-87de-d5e1d4dcfa8e'

AND c.`StartDate`>='2013-01' AND c.`EndDate`<='2013-02'

GROUP BY everyday

ORDER BY everyday

-- 3:根据科目的ID，来知道这个月，所有的同学的平均分，但是这个科目，我可能1号上课，2号没上，

-- 但是其他同学2号有课，那么2号也有了平均分，但是我不需要，这个就需要根据自己上课的日期来获取其他人的平均值

SELECT c.`SubjectId` ,AVG(Avescore) AS j_Avescore,AVG(Reviewscore) AS j_Reviewscore,AVG(Focusscore) AS j_Focusscore,AVG(Understandscore) AS j_Understandscore,

AVG(Applyscore) AS j_Applyscore,AVG(Mannerscore) AS j_Mannerscore,DATE_FORMAT(c.`StartDate`,'%Y-%m-%d') AS everyday

FROM `tb_fdt_courseitem` c INNER JOIN `tb_fdt_performance` p

ON c.`CourseItemId`=p.`CourseItemId`

WHERE c.`AgentId`='07551001'

AND c.`SubjectId`='1'

AND c.`StartDate`>='2013-01' AND c.`EndDate`<='2013-02'

GROUP BY everyday

ORDER BY everyday

 

我现在只是写了上面3个表，但是还没有真正的查询 ，下面是结合表二，和表三，来进行查询

SELECT DATE_FORMAT(temp2.everyday,'%d') AS `day`,

CONCAT(temp2.j_Avescore,"|",temp3.all_Avescore) AS Avescore,

CONCAT(temp2.j_Reviewscore,"|",temp3.all_Reviewscore) AS Reviewscore,

CONCAT(temp2.j_Focusscore,"|",temp3.all_Focusscore) AS Focusscore,

CONCAT(temp2.j_Understandscore,"|",temp3.all_Understandscore) AS Understandscore,

CONCAT(temp2.j_Applyscore,"|",temp3.all_Applyscore) AS Applyscore,

CONCAT(temp2.j_Mannerscore,"|",temp3.all_Mannerscore) AS Mannerscore

FROM tmp_2_self_avg_by_subjectid temp2 LEFT JOIN tmp_3_all_avg_by_subjectid temp3

ON temp2.everyday=temp3.everyday