mysql 怎么计算不同ID的最大日期与最小日期所对应的值的差值？

Question

表结构如图，只有id，value，time三个值字段，如何实现不同id的最大日期所对应数值与最小日期所对应数值的差值？（结果不管为正还是为负）

Answer 1

SELECT t3.id,t3.value-t4.value FROM
(SELECT t1.* FROM 表名 t1
INNER JOIN
(SELECT id,MAX(time) AS time FROM 表名 GROUP BY 1) t2
ON
t1.id=t2.id AND t1.time=t2.time
) t3
LEFT JOIN
(SELECT t1.* FROM 表名 t1
INNER JOIN
(SELECT id,min(time) AS time FROM 表名 GROUP BY 1) t2
ON
t1.id=t2.id AND t1.time=t2.time
) t4
ON t3.id=t4.id
GROUP BY 1
;