如何根据同一表中之前的数据更新SQL表 [英] How to update an SQL table based on the previous data in the same table

问题描述

我有一张表，用于衡量学生表现学生  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表？我应该加入吗？
谢谢。

解决方案

假设 testdate code> DATE 数据类型（而不是VARCHAR）

假设（id，testdate） tuple在学生中是UNIQUE

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

  SELECT t.id 
，t.testdate 
，t.performancestatus 
，（SELECT p.performancestatus 
 FROM学生p 
 WHERE p.id = t.id 
 AND p.testdate< t.testdate 
 ORDER BY p.testdate DESC 
 LIMIT 1 
）AS beforeperformancestatus 
 FROM student t 
 ORDER BY t.id，t.testdate 
  

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

 更新学生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 数据类型，或者是不以规范格式存储，然后少比比较不能保证将行限制到较早的测试日期。而且，order by不能保证返回最早的早期测试表。

对于第一个testdate，当没有更早的测试日期，子查询将返回NULL。我们可以使用一个表达式将NULL值转换为0.我们可以将子查询包含在一个函数中， IFNULL（< subquery>，0）

I have a table that measure the student performance student in my DB as below:

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

I want to update my table to have a new column PreviousPerformanceStatus. This PreviousPerformanceStatus is calculated based on the performanceStatus monitored, as below: Note: If there is not performanceStatus recorded before the TestDate, I want to make the 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

How do I update my SQL table? Should I use join? Thanks.

解决方案

Assuming that testdate column is DATE datatype (and not a VARCHAR)

And assuming that the (id,testdate) tuple is UNIQUE in student

We could use a correlated subquery in the SELECT list. As an example:

 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

Once we confirm that the SELECT statement gives us the result we are looking for, we can convert that to an UPDATE statement. Either as an inline view, or using the correlated subquery directly.

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
        )

If testdate is not DATE datatype, or is not stored in a canonical format, then then "less than" comparison is not guaranteed to restrict rows to an "earlier" testdate. And the "order by" is not guaranteed to return the most recent "earlier" testdate first.

For the "first" testdate, when there isn't an earlier testdate, the subquery will return NULL. We can use an expression to convert the NULL value to a 0. We could wrap the subquery in a function, IFNULL( <subquery> ,0)

这篇关于如何根据同一表中之前的数据更新SQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！