临时表的实际运用

{

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

“Subject”:

[

{

“Subject”:”科目”,

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

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

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

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

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

},

……

]

}

-- 用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