为每个用户选择最新的两行以获取列的差异(MySQL) [英] select the two latest rows for each user to get the difference of a column (MySQL)

问题描述

我的 mysql 查询有问题.我有一个表，其中包含一个 ID(自动增量)、一个用户 ID、一个值和以秒为单位的时间.现在我想获得每个用户的两个最低值的差异，但不知道该怎么做:(

I have a problem with a mysql query. I have a table with an ID (autoincrement), an user ID, a value and the time in seconds. Now I want to get the difference of the two lowest values for each user, but don't know how to do it :(

我有下表:

id   |  uid  | myvalue |  thetime
-----+-------+---------+-----------
13   |   1   |   120   | 1389287800
14   |   2   |   100   | 1389287810
15   |   3   |   110   | 1389287820
16   |   2   |    80   | 1389287830
17   |   4   |   100   | 1389287840
18   |   1   |   110   | 1389287850
19   |   3   |   105   | 1389287860
20   |   1   |   100   | 1389287870

我想得到以下结果:

uid  | difference      --> Order by difference
-----+------------
 2   |     20
 1   |     10
 3   |      5

如果有人愿意帮助我，那就太好了.非常感谢！！

It would be great, if someone is out to help me. Thank's a lot!!

推荐答案

你必须在这方面进行改进，但这基本上就是你如何做到的.您可以使用自连接

You have to improve on this, but this is basically how you do this. you can use a self-join

SELECT uid, MAX(myvalue)-MIN(myvalue) AS diff FROM
    (SELECT a.* FROM tablename AS a
        LEFT JOIN tablename AS a2
            ON a.uid=a2.uid AND a.myvalue<=a2.myvalue
        GROUP BY uid,myvalue
        HAVING COUNT(*)<=2
    ) a
GROUP BY uid

这篇关于为每个用户选择最新的两行以获取列的差异(MySQL)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！