假设我们有一个表来记录用户的权重和其他信息，如下所示：

health_indexes

id | user_id | weight | created_at
---+---------+--------+-----------
1  | 50      | 100    | 2020-01-01
2  | 50      | 98     | 2020-01-05
3  | 50      | 98.5   | 2020-01-10
4  | 50      | 92     | 2020-01-15
5  | 50      | 80     | 2020-01-20
.
.
.
10 | 100     | 130    | 2018-01-01
11 | 100     | 149999 | 2018-01-05
12 | 100     | 159999 | 2018-01-10
13 | 100     | 120    | 2018-01-15
.
.
.
20 | 200     | 87     | 2020-02-01
.
.
.
30 | 300     | 140    | 2020-01-01

我确实到了下表，但我正在寻找更好的方法：

user_id | first_weight | first_created_at | last_weight | last_created_at
--------+--------------+------------------+-------------+----------------
50      | 100          | 2020-01-01       | 80          | 2020-01-20
100     | 130          | 2018-01-01       | 120         | 2018-01-15

查询：

select u.id user_id,
    (select weight from health_indexes where user_id = u.id order by created_at limit 1) first_weight,
    (select created_at from health_indexes where user_id = u.id order by created_at limit 1) first_created_at,
    (select weight from health_indexes where user_id = u.id order by created_at desc limit 1) last_weight,
    (select created_at from health_indexes where user_id = u.id order by created_at desc limit 1) last_created_at
from users u
group by u.id
having first_weight > last_weight
order by (first_weight - last_weight) desc
limit 50;

我正在寻找一种方法，在health\u索引上加入两次，以获得相同的结果。有什么想法吗？