我下面有下面的sql

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
FROM ttstudent
INNER JOIN student ON ttstudent.studentid = student.code
INNER JOIN ondemand ON ttstudent.studentid = ondemand.Student_ID
GROUP BY ondemand.Student_ID

这会为大约 25 人生成一个柱状列表，其中最后一列是计算字段，用于查找表中最后 2 个值之间的差异。分数带有时间戳。

CODE |Year7Feb|Year7Jul|Year8Feb|Year8Jul|Year9Feb|Year9Jul| Year10Feb| Growth
abe1 |    2.3 |   2.9  |        |        |        |        |          |   .6
bas1 |        |        |   3.5  |   3.7  |        |        |          |   .2
cod  |        |        |        |        |        |  4.5   |    5.2   |   .7

我想做的是添加另一列，该列将从每个用户(无论它在哪一列)中获取最后两个分数并找出差异。我将此列称为增长。

除了 max.有什么想法吗？

最佳答案

这应该可以解决问题:

使用以下查询计算列 growth

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;

我省略了 JOIN 到表 student 和 ttstudent 及其列

ttstudent.ttstudentid,
ttstudent.studentid,
ttstudent.subjectid,
ttstudent.classnumber,
ttstudent.classid,
concat(student.fn, " ", student.sn) AS Student

编辑:

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

编辑2:

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

无论如何，这是一个 SQLfiddle 来演示整个事情(这里是一个修改版本:sqlfiddle2)。

第三次也是最后一次编辑:

您可能想要的是类似于 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

我以前的版本反射(reflect)了我个人的偏好，即避免任何形式的冗余，以及我尽可能将事物参数化的野心。但我可能有点过头了 ;-)。

关于mysql - 查找两个字段之间的差异作为表中的列，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/18032435/