mysql取两表列的差异_mysql – 在表中找到两个字段之间的差异作为列

这应该做的伎俩：

使用以下查询计算列增长

SELECT si,ty,la.uid laid,pr.uid prid,(la.score-pr.score) growth FROM (

SELECT si,ty,max(test_date) cyprev, cylast FROM ondemand INNER JOIN (

SELECT Student_ID si,type ty,max(test_date) cylast FROM ondemand

GROUP BY Student_ID,type

) od ON si=Student_ID AND ty=type AND cylast>test_date

GROUP BY si,ty, cylast

) getlast2

INNER JOIN ondemand la ON la.Student_Id=si AND la.type=ty AND la.test_date=cylast

INNER JOIN ondemand pr ON pr.Student_Id=si AND pr.type=ty AND pr.test_date=cyprev

然后LEFT JOIN加入您的整体查询(这里略微简化的版本)：

SET @subj:="Numeracy";

SELECT Student_id,

SUM(If(ondemand.cycle="Feb7" and ondemand.type=@subj, ondemand.Score, NULL)) AS `Yr7 Feb`,

SUM(If(ondemand.cycle="Jul7" and ondemand.type=@subj, ondemand.Score, NULL)) AS `Yr7 July`,

SUM(If(ondemand.cycle="Feb8" and ondemand.type=@subj, ondemand.Score, NULL)) AS `Yr8 Feb`,

SUM(If(ondemand.cycle="Jul8" and ondemand.type=@subj, ondemand.Score, NULL)) AS `Yr8 July`,

SUM(If(ondemand.cycle="Feb9" and ondemand.type=@subj, ondemand.Score, NULL)) AS `Yr9 Feb`,

SUM(If(ondemand.cycle="Jul9" and ondemand.type=@subj, ondemand.Score, NULL)) AS `Yr9 July`,

SUM(If(ondemand.cycle="Feb10" and ondemand.type=@subj, ondemand.Score, NULL)) AS `Yr10 Feb`,

SUM(If(ondemand.cycle="Jul10" and ondemand.type=@subj, ondemand.Score, NULL)) AS `Yr10 Aug`,

growth

FROM ondemand LEFT JOIN (

SELECT si,ty,la.uid laid,pr.uid prid,(la.score-pr.score) growth FROM (

SELECT si,ty,max(test_date) cyprev, cylast FROM ondemand INNER JOIN (

SELECT Student_ID si,type ty,max(test_date) cylast FROM ondemand

GROUP BY Student_ID,type

) od ON si=Student_ID AND ty=type AND cylast>test_date

GROUP BY si,ty, cylast

) getlast2

INNER JOIN ondemand la ON la.Student_Id=si AND la.type=ty AND la.test_date=cylast

INNER JOIN ondemand pr ON pr.Student_Id=si AND pr.type=ty AND pr.test_date=cyprev

) gt ON si=Student_id AND ty=@subj

GROUP BY Student_id;

我把JOINs留给了表学生和ttstudent以及他们的专栏

ttstudent.ttstudentid,

ttstudent.studentid,

ttstudent.subjectid,

ttstudent.classnumber,

ttstudent.classid,

concat(student.fn, " ", student.sn) AS Student

编辑：

刚刚使用test_date列进行了更改.子查询在MySQL中进行了测试,我希望它也适用于您的数据库.

EDIT2：

我慢慢看到你来自哪里.它变得越来越复杂(只是填写了关于type =“Numeracy”的xtra条件……也许毕竟有一个更简单的解决方案？

无论如何,这是一个SQLfiddle来展示整个事情(这里是一个修改版本：sqlfiddle2).

第3次和最后编辑：

您可能想要的是SQLfiddle3( – >只有一个没有前面的SET语句的SELECT命令).你的完整命令应该是这样的：

SELECT

ttstudent.ttstudentid,

ttstudent.studentid,

ttstudent.subjectid,

ttstudent.classnumber,

ttstudent.classid,

concat(student.fn, " ", student.sn) AS Student,

SUM(If(ondemand.cycle="Feb7" and ondemand.type="Numeracy", ondemand.Score, NULL)) AS `Yr7 Feb`,

SUM(If(ondemand.cycle="Jul7" and ondemand.type="Numeracy", ondemand.Score, NULL)) AS `Yr7 July`,

SUM(If(ondemand.cycle="Feb8" and ondemand.type="Numeracy", ondemand.Score, NULL)) AS `Yr8 Feb`,

SUM(If(ondemand.cycle="Jul8" and ondemand.type="Numeracy", ondemand.Score, NULL)) AS `Yr8 July`,

SUM(If(ondemand.cycle="Feb9" and ondemand.type="Numeracy", ondemand.Score, NULL)) AS `Yr9 Feb`,

SUM(If(ondemand.cycle="Jul9" and ondemand.type="Numeracy", ondemand.Score, NULL)) AS `Yr9 July`,

SUM(If(ondemand.cycle="Feb10" and ondemand.type="Numeracy", ondemand.Score, NULL)) AS `Yr10 Feb`,

SUM(If(ondemand.cycle="Jul10" and ondemand.type="Numeracy", ondemand.Score, NULL)) AS `Yr10 Aug`,

ondemand.Student_ID,

getdif.growth

FROM ttstudent

INNER JOIN student ON ttstudent.studentid = student.code

INNER JOIN ondemand ON ttstudent.studentid = ondemand.Student_ID

LEFT JOIN (

SELECT si,ty,la.uid laid,pr.uid prid,(la.score-pr.score) growth FROM (

SELECT si,ty,max(test_date) cyprev, cylast FROM ondemand INNER JOIN (

SELECT Student_ID si,type ty,max(test_date) cylast FROM ondemand

GROUP BY Student_ID,type

) od ON si=Student_ID AND ty=type AND cylast>test_date

GROUP BY si,ty, cylast

) getlast2

INNER JOIN ondemand la ON la.Student_ID=si AND la.type=ty AND la.test_date=cylast

INNER JOIN ondemand pr ON pr.Student_ID=si AND pr.type=ty AND pr.test_date=cyprev

) getdif ON si=ondemand.Student_ID AND ty=ondemand.type

WHERE ondemand.type='Numeracy'

GROUP BY ondemand.Student_ID

我以前的版本反映了我个人偏好避免任何冗余,以及我尽可能多地参数化的野心.但我可能走得太远了;-).