MySql:获取下一行值

Question

这是表中的示例数据集:

|  callid    |   called     |   calling   |        end          | 
|------------|--------------|-------------|---------------------|
| 1411482360 | 001143792042 | 08444599175 | 2014-07-31 13:55:03 |
| 1476992122 | 001143792042 | 08441713191 | 2014-07-31 14:05:10 |

到目前为止，我已经成功获取了前一行的值，并且我还需要下一行的值。这是我的查询:

SELECT c.called as customer, c.calling as company, DATE_FORMAT(c.`end`,'%Y-%m-%d') as _date,
        @G := if(@prevComp <> c.calling AND @prevCust = c.called AND @prevDate = DATE_FORMAT(c.`end`,'%Y-%m-%d'), 1, 0) as Gain,
        @prevCust := c.called as prevCust,
        @prevComp := c.calling as prevComp,
        @prevDate := DATE_FORMAT(c.`end`,'%Y-%m-%d') as prevDate
FROM cdrdata_archive c CROSS JOIN
     (SELECT @prevComp := NULL, @prevCust := NULL, @prevDate := NULL) prevVals
ORDER BY c.called, c.`end` 

上面的查询返回以下结果集:

Customer     Company      Date        prevCust     prevComp     prevDate     Gain

00140443360  08434599117  2014-01-28  00140443360  08434599117  2014-01-28   0
00475172558  08434599218  2014-01-27  00475172558  08434599218  2014-01-27   0
00475172558  08434593455  2014-01-27  00475172558  08434599118  2014-01-27   1

我需要上一行和下一行的值来计算每家公司丢失和获得的调用。我设法获得了正确的增益值，但是要计算 Lost 值(类似于 Gain 但在新列中)，我需要下一个客户、公司和日期。

是否可以在没有太多成本的情况下将其安装到此查询中，或者我是否需要重写它？

Answer 1

使用 MySQL 进行研究 LIMIT条款。返回的行带有 ORDER BY所以你确实有一个订单，但你没有“列表中的数字”，其中 LIMIT 是

因此您有了查询 - 并且在 SQL 末尾附加了

LIMIT $x, 1你在哪里LIMIT <offset>, <count>因此，您的计数是您的行 (1)，每次您转到“下”行时，您都会更改

$x = $x + 1

希望这有帮助。 http://www.mysqltutorial.org/mysql-limit.aspx

因此，对于您显示的类型的第三个查询将是:

SELECT c.called as customer, c.calling as company, DATE_FORMAT(c.`end`,'%Y-%m-%d') as _date,
        @G := if(@prevComp <> c.calling AND @prevCust = c.called AND @prevDate = DATE_FORMAT(c.`end`,'%Y-%m-%d'), 1, 0) as Gain,
        @prevCust := c.called as prevCust,
        @prevComp := c.calling as prevComp,
        @prevDate := DATE_FORMAT(c.`end`,'%Y-%m-%d') as prevDate
FROM cdrdata_archive c CROSS JOIN
     (SELECT @prevComp := NULL, @prevCust := NULL, @prevDate := NULL) prevVals
ORDER BY c.called, c.`end` LIMIT 2,1