如何获取多行上最新日期和最早日期之间的列的差异

Here's the columns for table users.

这是表用户的列。

+--------+-----------------+------+-----+---------+----------------+
| Field  | Type            | Null | Key | Default | Extra          |
+--------+-----------------+------+-----+---------+----------------+
| uid    | int(6) unsigned | YES  |     | NULL    |                |
| score  | decimal(6,2)    | YES  |     | NULL    |                |
| status | text            | YES  |     | NULL    |                |
| date   | datetime        | YES  |     | NULL    |                |
| cid    | int(7) unsigned | NO   | PRI | NULL    | auto_increment |
+--------+-----------------+------+-----+---------+----------------+

I want the difference between a user's most current score and earliest score. I tried:

我想要用户最新得分和最早得分之间的差异。我试过了：

select co1.uid, co1.score, co1.date from users as co1, (select uid, score, min(date) from users group by uid) as co2 where co2.uid = co1.uid;

This does not work. I also tried

这不起作用。我也试过了

select co1.uid, co1.score, co1.date from users as co1, (select uid, score, max(date) - min(date) from users group by uid) as co2 where co2.uid = co1.uid;

Result I get:http://pastebin.com/seR81WbE

结果我得到：http：//pastebin.com/seR81WbE

Result I want:

结果我想：

uid  max(score)-min(score)
1        40
2       -60
3        23

etc

等等

1 个解决方案

#1

I think the simplest solution is two joins:

我认为最简单的解决方案是两个连接：

select u.uid, umin.score, umax.score
from (select uid, min(date) as mind, max(date) as maxd
      from users
      group by uid
     ) u join
     users umin
     on u.uid = umin.uid and umin.date = u.mind join
     users umax
     on u.uid = umax.uid and umax.date = u.maxd;

I should note: if you know the scores are only increasing, you can do the much simpler:

我应该注意：如果你知道分数只是增加，你可以做得更简单：

select uid, min(score), max(score)
from users
group by uid;

#1