数据库表中每一行的约简

Question

我有一张临时表格，如下所示：

student    |      Data     |    number           
-----------|---------------|--------------
1          |   book        |      2          
1          |   book        |      5    
1          |   book        |      9     
2          |   book        |      1         
2          |   book        |      5     

我将在like as output列中显示列缩减，如下所示：

student    |   Data        |    number      |output (number column of next row-previous line )
-----------|---------------|----------------|--------------
1          |   book        |      2         |     0
1          |   book        |      5         |     3  (result of (5-2=3)
1          |   book        |      9         |     4  (result of (9-5=4)
2          |   book        |      1         |     0
2          |   book        |      5         |     4  (result of (5-1=4)

怎么写php的脚本是正确的？因为我很困惑

Answer 1

下面的脚本将从同一个学生的前一个数字中减去这个数字。下面是如何在MySQL (它不支持窗口函数)中做到这一点。

SELECT 
    t1.student, 
    t1.Data, 
    t1.number, 
    IF (t2.number IS NULL, 0, t1.number - MAX(t2.number)) as output 
FROM 
    tbl t1 
LEFT JOIN 
    tbl t2 
ON 
    t1.student = t2.student 
    AND t1.number > t2.number 
GROUP BY 
    t1.student, t1.Data, t1.number

这是SQL Fiddle

Answer 2

您没有提到您的DBMS，所以这是标准的SQL：

select student,
       data, 
       number,
       number - lag(number,1,number) over (partition by student order by id) as output
from the_table
order by student, id

SQLFiddle example