MySQL - Calculate the difference between rows [duplicate]

This question already has an answer here:

这个问题在这里已有答案:

• MySQL difference between two rows of a SELECT Statement 4 answers

MySQL语句4的两行之间的差异4答案

So I'm working with MySQL and PHP. Having some trouble trying to get a query that calculates the difference between consecutive rows.

所以我正在使用MySQL和PHP。尝试获取计算连续行之间差异的查询时遇到一些麻烦。

What I need is to get the difference between each row for: data_views, data_sub & data_videos for each channel URL.

我需要的是获得每一行之间的差异:data_views,data_sub和data_videos每个频道的URL。

Much appreciated if anyone can help :)

非常感谢,如果有人可以帮助:)

Here's my table structure:

这是我的表结构:

1 个解决方案

#1

Try this..if you don't want ordering just remove ORDER BY A.channel_url clause..

试试这..如果你不想订购只需删除ORDER BY A.channel_url子句..

SELECT 
  A.*,
  A.data_views - IFNULL(B.data_views, A.data_views) AS data_views_diff,
  A.data_subs - IFNULL(B.data_subs, A.data_subs) AS data_subs_diff,
  A.data_videos - IFNULL(B.data_videos, A.data_videos) AS data_videos_diff 
FROM
  my_table A 
  LEFT JOIN my_table B 
    ON B.id = 
    (SELECT 
      MIN(id) 
    FROM
      my_table C 
    WHERE C.id > A.id 
      AND C.id != A.id 
      AND C.channel_url = A.channel_url) 
ORDER BY A.channel_url;

Check Fiddle