为每个用户选择最新的两行以获取列的差异(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