mysql - 如何根据同一张表中的先前数据更新 SQL 表

Question

我的数据库中有一个衡量学生表现 student 的表格，如下所示:

ID  TestDate    PerformanceStatus (PS)
1   15/03/2016  0
1   01/04/2016  2
1   05/05/2016  1
1   07/06/2016  1
2   15/03/2016  0
2   01/04/2016  2
2   05/05/2016  1
2   07/06/2016  3
2   23/08/2016  1

我想更新我的表以包含一个新列 PreviousPerformanceStatus。这个 PreviousPerformanceStatus 是根据监控到的 performanceStatus 计算出来的，如下:注意:如果在 TestDate 之前没有记录 performanceStatus，我想使 PreviousPerformanceStatus = PerformanceStatus

ID  TestDate    PS  PreviousPerformanceStatus
1   15/03/2016  0   0
1   01/04/2016  2   0
1   05/05/2016  1   2
1   07/06/2016  1   1
2   15/03/2016  0   0
2   01/04/2016  2   0
2   05/05/2016  1   2
2   07/06/2016  3   1
2   23/08/2016  1   3

如何更新我的 SQL 表？我应该使用加入吗？谢谢。

Answer 1

假设testdate列是 DATE数据类型(而不是 VARCHAR)

并假设(id,testdate)元组在 student 中是唯一的

我们可以在 SELECT 列表中使用相关子查询。例如:

 SELECT t.id
      , t.testdate
      , t.performancestatus
      , ( SELECT p.performancestatus
            FROM student p
           WHERE p.id = t.id
             AND p.testdate < t.testdate
           ORDER BY p.testdate DESC
           LIMIT 1
        ) AS previousperformancestatus
 FROM student t
ORDER BY t.id, t.testdate

一旦我们确认 SELECT 语句为我们提供了我们正在寻找的结果，我们就可以将其转换为 UPDATE 语句。作为内联 View ，或直接使用相关子查询。

UPDATE student t
   SET t.previousperformancestatus
       = ( SELECT p.performancestatus
            FROM student p
           WHERE p.id = t.id
             AND p.testdate < t.testdate
           ORDER BY p.testdate DESC
           LIMIT 1
        )

如果testdate 不是 DATE数据类型，或者不以规范格式存储，则“小于”比较不能保证将行限制为“较早”的测试日期。并且“排序依据”不能保证首先返回最近的“较早”测试日期。

对于“第一个”测试日期，当没有更早的测试日期时，子查询将返回 NULL。我们可以使用表达式将 NULL 值转换为 0。我们可以将子查询包装在一个函数中，IFNULL( <subquery> ,0)