找到两行之间的最大差异

I have a table that stores values each hour from a website based on 100 users. Each user is there each hour, but might have a higher/lower value.

我有一个表，每小时从基于100个用户的网站存储值。每个用户每小时都在那里，但可能有更高/更低的值。

I want to find the highest difference between the rows and output them.

我想找到行之间的最大差异并输出它们。

mysql_query("SELECT `username`,`energy` FROM `hoflevel`");

I know I could loop through all the rows of each user and find the highest value, but that is too much code. I'm sure there is a way to figure out the biggest difference between two rows, grouped by the username. It doesn't have to be only each hour. It would be like a "Alan set the record of 345,048 Energy at 5am yesterday!".

我知道我可以循环遍历每个用户的所有行并找到最高值，但这是太多的代码。我确信有一种方法可以找出两行之间的最大差异，按用户名分组。它不必每小时一次。这就像是“艾伦昨天凌晨5点创造了345,048能量纪录！”。

1 个解决方案

#1

This gets the maximum difference in energy per user, ignoring users with only a single record:

这可以获得每个用户的最大能量差异，忽略只有一条记录的用户：

SELECT hoflevel.username, MAX(hoflevel.energy-h.energy) as maxDiff 
FROM hoflevel 
LEFT JOIN hoflevel h 
ON h.username=hoflevel.username AND hoflevel.energy>=h.energy
WHERE h.energy IS NOT NULL
GROUP BY hoflevel.username;

Basically, it joins hoflevel to itself on username which produces a table with username and every pair of energy values for that user.

基本上，它在用户名上加入hoflevel自身，生成一个包含用户名的表和该用户的每对能量值。

Then it works out the maximum difference between energies grouped by username.

然后它计算出按用户名分组的能量之间的最大差异。

Note, you could further save some computation by ignoring users who may have multiple records in the database but the same energy each time (and so their maxDiff is 0) by changing the >= to a > in the join condition.

注意，通过在连接条件中将> =更改为>，忽略可能在数据库中具有多个记录但每次具有相同能量（因此其maxDiff为0）的用户，可以进一步节省一些计算。

#1