php - 如何在mysql中找到更大的增加值

Question

我有一个历史表

user  | point | date 
john  | 100   | 2015-01-01 
smith | 90    | 2015-01-01 
adam  | 95    | 2015-01-01 
john  | 120   | 2015-01-02 
smith | 115   | 2015-01-02 
adam  | 105   | 2015-01-02
john  | 125   | 2015-01-03 
smith | 120   | 2015-01-03 
adam  | 135   | 2015-01-03

在此表中，我想找到在表的最后 3 天按增加点排序的用户列表，因此输出将是:

adam  | 40
smith | 30
john  | 25

我如何使用 php 和 mysql 做到这一点？

Answer 1

一种方法是使用表的自连接，指定开始和结束日期。

SELECT t1.user, t1.point - t2.point AS diff
FROM yourTable AS t1
JOIN yourTable AS t2 ON t1.user = t2.user
WHERE t1.date = '2015-01-03'
AND t2.date = '2015-01-01'
ORDER BY diff DESC