在php中比较当前行值和下一行值

kr98yfug 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(323)

ID UserID    Payment_Date  Expired_Date      Diff(Payment_Date&Expired_Date)

 1  123   2017-08-01 00:00:00  2018-01-01 00:00:00             
 2  123   2018-02-01 00:00:00  2018-10-01 00:00:00              31    
 3  123   2018-10-05 00:00:00  2018-10-10 00:00:00              4

如何比较第一行到期日期和第二行付款日期。我需要像diff一样显示结果( Payment_Date & Expired_Date ) 31,4 .

mysql php

来源：https://stackoverflow.com/questions/50362496/compare-current-row-value-with-next-row-value-in-php

1条答案

按热度按时间

lhcgjxsq1#

不需要php，你可以用sql来实现

SELECT ID, UserID, Payment_Date, Expired_Date, lastpayement, DATEDIFF(lastpayement, Expired_Date) AS DIFF
FROM
(
    SELECT m.ID, m.UserID, m.Payment_Date, m.Expired_Date, 
            (
                SELECT Payment_Date
                FROM mytable tt
                WHERE  m.Expired_Date < tt.Payment_Date AND tt.UserID=m.UserID
                GROUP BY UserID
            ) AS lastpayement
    FROM mytable m
) t

结果

| ID | UserID |         Payment_Date |         Expired_Date |         lastpayement |   DIFF |
|----|--------|----------------------|----------------------|----------------------|--------|
|  1 |    123 | 2017-08-01T00:00:00Z | 2018-01-01T00:00:00Z | 2018-02-01T00:00:00Z |     31 |
|  2 |    123 | 2018-02-01T00:00:00Z | 2018-10-01T00:00:00Z | 2018-10-05T00:00:00Z |      4 |
|  3 |    123 | 2018-10-05T00:00:00Z | 2018-10-10T00:00:00Z |               (null) | (null) |

sqlfiddle公司

赞(0）回复(0）举报 2021-06-21

我来回答

在php中比较当前行值和下一行值

1条答案

相关问题

热门标签

最新问答